Thanks Taifun and TIMAI2, Alright will try to sort from the app and let you know
have you any other ideas please
i cant arrange data from it any written text then the already written in the pic isnt working
let me show you my request and tell me what can i do with your creativity
there are about 10 columns from the 12 columns in the sheet needs to be arranged with the similar cells
similar date, time, so on as you see in the screenshot
and after arranging the data i need to get the sum of num people column for the similar arranged
for example
1 order 1 jan 8am from egypt to usa
2 order 20dec 11am from egypt to mexico
3 order 15feb 8pm from mexico to egypt
so it needs to be arranged like that
1 order 1 jan 8am from egypt to usa
3 order 15feb 8pm from mexico to egypt
2 order 20dec 11am from egypt to mexico
i dont care about where can i arrange them inside the app or outside, the only important thing is to arrange multiple columns with the similar so if it can be by block and then show them arranged in the listview directly it will be great
???
Not understandable, sorry
Make yourself familiar with the sql language, read again Query Language Reference (Version 0.7) | Charts | Google for Developers
It looks like you want to sort by date... find the date column, from the spreadsheet it seems to be column A, i.e.
ORDER BY A
So which column is it? Sorry, I can't read your language... if you want help, then let me suggest you to make it as easy as possible for others to help...
Taifun
i tried the run query by adding arranging columns but didnt work, the only thing working is the text he wrote which is select a,b,c ,,,,
lets show me how to arrange listview elements blocks something like that but by blocks
when ListView.BeforeSorting
sort list by column "Date"
sort list by column "Time"
sort list by column "Trip Type"
sort list by column "Car Type"
when ListView.AfterSorting
foreach item in list
if item = previous item
sum += item.NumberOfPassengers
else
add to list sum
previous item = item
that way will be more better for me than the query solution or the google sheet scripts
Maybe you need a grouped sum procedure?
You forgot to show us what you tried
Yes, you can sort by multiple columns and aggregate data, for example
select A, sum(B) group by A
What about providing an example table in English like this
tip: convert your column names into English in camelCase notation, i.e. Trip Type --> tripType and be clear about how the data should be aggregated
for example
startDate, start, end, passengers
2024-12-29 egypt usa 2
2024-12-30 egypt mexico 3
2024-12-29 egypt usa 3
2024-12-29 egypt usa 4
SELECT startDate, start, end, sum(passengers)
GROUP BY startDate, start, end ORDER BY startDate
would result in
startDate, start, end, sum(passengers)
2024-12-29 egypt usa 9
2024-12-30 egypt mexico 3
Taifun
thanks so much for your interest, i tried too much but didnt get it
here is my sheet
lets try to sort the similar data inside the columns
date
time
cartype
location
arrive
traveltype
insideoroutside
and then get the sum for the similar rows for the peoplenumber
so lets do my a favor and sort it
if you can sort it by query it will be great, if it couldnt then lets
send me such that blocks for sorting the listview elements for that columns data
am still a beginner in the app so its hard to get it from the first time so lets guide me
you are mixing 2 different things...
these blocks from your screenshot look like some blocks from @ABG, which are for doing sql stuff in lists, this is not what you need
your data is stored in a Google Spreadsheet, you access it using gviz as described in the tutorial here
the documentation of the query language is here Query Language Reference (Version 0.7) | Charts | Google for Developers
something like this
select name, number, date, time, sum(oeoplenumber)
will not work... if you want to use aggregate functions like sum, then you have to group the data for all other columns, for example
select name, number, date, time, sum(peoplenumber)
group by name, number, date, time
Taifun
select date, time, carype, location, arrive, traveltype, insideoroutside, sum(peoplenumber) group by date, time, carype, location, arrive, traveltype, insideoroutside
Taifun
Hi, thanks all of you for your effores, just do me a favor please as am confused and its my last step in my project,
lets send me a sample aia file showing arranging the list view elements blocks for the tagreted rows i show you before as below
lets try to sort the similar data inside the columns
date
time
cartype
location
arrive
traveltype
insideoroutside
and then get the sum for the similar rows for the peoplenumber
Arranging the list view elements something like that not sure but close
thanks in advance for your help, really appreciate it
yes i tried it let me show you
here is my aia file with my google sheet table
and here is my trying, i tried the same equation you sent
and the example above i didnt mean it but something like by blocks let me show you my point
am getting the data from the listview as below by items and number for items
so i thought if there is a way to sort rows with that blocks using the items and thier numbers
using a block like below
maybe i didn't show my point well but if you can try the aia file i sent and make the query sort working it will be nice from you
It is cartype and not carype... check your column names
Get a simple query working like select *
See also How to show raw responseContent from your app when debugging
Taifun
There is no need to sort rows anymore...
You send the correct sql statement to the spreadsheet and already get the sorted and aggregated result back
Taifun
This may be the alternative option. But, since you prefer to code, this may not help.
thanks it worked when i added columns by as below
select D, E, G, I, J, K, L, sum(F) group by D, E, G, I, J, K, L
but it didn't arrange the date lets see below
Don't store the date in text like 6january or 19june use a sortable text format like YYYY-MM-DD which includes year month and day to get a reasonable result also at the end of the year
2024-02-06
2024-06-19
To sort your data add additionally an ORDER BY clause, for example
select D, E, G, I, J, K, L, sum(F) group by D, E, G, I, J, K, L order by D,E,G,I,J,K,L
Taifun
1 Like
thanks so much its arranging the date now but the time is still no so which format needs to be as well and what about the other texts if they are arabic, will it arrange the text with the similar texts so its familiar with English only?