Search code examples
google-sheetsgoogle-sheets-formulagoogle-sheets-querygoogle-query-language

How to query(traverse) along a wide format table?

I have a table like that.

Table example

I can't get my head around how to traverse(query selectively) through the countries and output data per date. Sometimes I need one country, sometimes 3 or 5.
I just need to have a separate table where I'll have something like that for later processing (charts and other calculations). enter image description here

Usually, I'd use something like 

=QUERY(A1:100, "SELECT a column WHERE a row ='Name of a country'")

or

=TRANSPOSE(QUERY(A1:100, "Select * WHERE A = 'Australia'"))

But it looks like this logic doesn't work here. Any ideas?

Here is a dummy sheet

P.S. The second Cumulative column it's just an example of calculations that I'll need to do later with each country numbers. I'll just add =iferror(B3+C2,"0") Although if you have any better ideas on how to optimise that, I'll be happy to hear it.

Solution

  • try:

    =QUERY(TRANSPOSE(QUERY(TRANSPOSE(Sheet4!A:AQ), 
 "where Col1 matches 'Date|Australia|India'", 0)), 
 "where Col1 >= "&DATEVALUE("2020-1-23")&" 
    and Col1 <= "&DATEVALUE("2020-1-30"), 1)

    0

    and the cumulatives (running totals) can be done like:

    =ARRAYFORMULA(IF(O3:O="",,SUMIF(ROW(O3:O), "<="&ROW(O3:O), O3:O)))

    0