Google sheets double query with selecting cell value as an explicit where condition

Question

I have the following dataset:

Start day   End Day Product ID  Sells   Unites
01/11/2018  08/11/2018  Blue    DA_1000 $55,555.00  12
01/11/2018  08/11/2018  Red DA_1000 $1,321,321.00   55555
01/11/2018  08/11/2018  Blue    DA_1000 $444.00 11111
01/11/2018  08/11/2018  Green   DA_1000 $1,313,121.00   24
01/11/2018  08/11/2018  Aspire  DA_1001 $11.00  55555
09/11/2018  16/11/2018  Black   DA_1001 $786.13 49
09/11/2018  16/11/2018  Apple   DA_1001 $629.55 22
09/11/2018  16/11/2018  White   DA_1001 $0.39   1
17/11/2018  24/11/2018  Orange  DA_1002 $0.00   515413
17/11/2018  24/11/2018  Blue    DA_1002 $569.35 42
17/11/2018  24/11/2018  Red DA_1003 $315,154.00 40
25/11/2018  30/11/2018  White   DA_1003 $45,464,564.00  36
25/11/2018  30/11/2018  Black   DA_1003 $490.41 42
25/11/2018  30/11/2018  Vol DA_1004 $435.61 55

And I want to query from the products that have sells equal to=444, from this query I want to query again all of those that they're ID is equal to "DA_1000". and I managed to do that. But when I trying inserting to the where condition the explicit cell value I can't get any result,

This is the working query:

=query({query(A1:F15,"Select * Where E=444",1)},Select * Where Col4='DA_1000',1)

This is the non-working query:

=query({query(A1:F15,"Select * Where E=444",1)},Select * Where Col4='TEST!N6',1)

This is the url for the google sheet.

Please assist.

score 0 · Accepted Answer · answered Nov 21 '18 at 11:14

0

If you would want the select-clause in a cell (ex: H10) you'd have to write in this cell:

="Select * Where Col4='"&TEST!N6&"'"

Then in your query:

=query({query(A1:F15,"Select * Where E=444",1)},H10,1)

Note that this can also be done in one query by writing in the reference cell

=" and D='"&TEST!N6&"'"

and then your query would look like this:

=query(A1:F15,"Select * Where E=444"&H10,1)

answered Nov 21 '18 at 11:14

JPV

26,499
4
33
48

This works, but this was a simplification. In a more complex query, aka: [SELECT P,Q,SUM(R),SUM(S),SUM(T),SUM(R)/SUM(S),SUM(R)/SUM(T),SUM(T)/SUM(S) GROUP BY P,Q order by Q] replacing the the part of the original: query(A1:F15,"Select * Where E=444",1). I can't seem to utilise the same concept – Rapha Nov 21 '18 at 12:26

score 0 · Answer 2 · answered Nov 21 '18 at 15:21

0

Maybe:

=query(A:F,"where E=444 and D contains 'DA_1000'")

would serve.

answered Nov 21 '18 at 15:21

pnuts

58,317
11
87
139

Google sheets double query with selecting cell value as an explicit where condition

2 Answers2