CODEWITHSUNDEEP
sqlgoogle-sheets
Peter StJ
Peter StJ

Reputation: 2417

How to use substring and charindex in google query language

I have a google spreadsheet sheet with several columns:

A: date B: string C: number ... G: string (could be empty) H: string (could be empty)

I would like to have a small table with the following:

Get the sum of C in table, where rows are the values of G (substring of it, as they are configured as this: CATEGORY:ITEM, I need grouping by CATEGORY only) and columns are monts

So far I've got only partial solutions with query (for example group by month(toDate(A)) etc) - I seem to be unable to use substring and charindex nor left to do string manipulation to remove the item part after the category nor to visualize those as the first clumns in the resulting rows...

Edit: just to clear up a bit: I need to alter the value in G for each row so that I can group by the altered value. I know it is possible to do with dates ( in my example -> group by month(toDate(A)) gives me access to each value in A column so the result is grouped correctly for each separate month). But it seems string manipulation is not allowed?

How do I do that for starters.

Thanks

Upvotes: 1

Views: 10142

Answers (2)

Geoff
Geoff

Reputation: 1

been searching all over for a similar solution :-)

just in case you would like an alternative solution... This works a treat based on my sheet H with data in column A to F

=transpose(split(textjoin("|",1,transpose({H!A1:F100})),"|"))

See... https://webapps.stackexchange.com/questions/90629/concatenate-several-columns-into-one-in-google-sheets

Upvotes: -1

JPV
JPV

Reputation: 27262

Based on your desired output, can you try this on sheet 2:

=ArrayFormula(query({day(Sheet1!A2:A10)&text(month(Sheet1!A2:A10), " (mmm)"), Sheet1!B2:F10, regexextract(Sheet1!G2:G10, "(.+):")}, "select Col7, sum(Col3) group by Col7 pivot Col1"))

and see if this is getting somewhere ?

Or in case you prefer open ended ranges:

=ArrayFormula(query({day(Sheet1!A2:A)&text(month(Sheet1!A2:A), " (mmm)"), Sheet1!B2:F, iferror(regexextract(Sheet1!G2:G, "(.+):"))}, "select Col7, sum(Col3) where Col7 <>'' group by Col7 pivot Col1"))

Upvotes: 3

Related Questions