I have a Google Sheets file which has two sheets using ImportRange
formulas to collect data from a separate file. These seem to work fine, though I did have to use two ImportRange
formulas for each sheet (one for the header row and one for a portion of the data) because of how big the sheets are.
On another worksheet, I am using a couple unioned queries to pull relevant data from these two imported ranges into the same table/range. I've got it working reasonably well but for some reason, the query for one of the sheets is insisting on reporting a row of data that is not relevant to the query criteria, and two of the values (one of which is a date used in the criteria) is blank and I can't figure out why.
Troubleshooting and results:
ImportRange
formula for the source data to exclude this problematic row, the problematic row will shift to a new one that wasn't problematic before (and those two source values are also not blank).Combined query formula:
=QUERY(
{ QUERY('K+C Production Data Import'!A:M, "Select E, D, G, I, J, K, L WHERE I = DATE '" &
TEXT(C2, "yyyy-MM-dd") &
"' ORDER BY E LABEL D 'Shift', E 'Line', G 'Lot#', I 'Prod Date', J 'Units', K 'Pack Type', L 'SKU'", 1);
QUERY('F Production Data Import'!A3:M, "Select E, D, G, I, J, K, L WHERE I = DATE '" &
TEXT(C2,"yyyy-MM-dd") & "'", 1)
},
"SELECT * WHERE Col3 IS NOT NULL ORDER BY Col3" )
Any guidance or suggestions?
Thanks!
Troubleshooting sub-form combined here:
try:
=QUERY({QUERY('K+C Production Data Import'!A:M,
"select E,D,G,I,J,K,L
where I = DATE '"&TEXT(C2, "yyyy-MM-dd")&"'
order by E
label D'Shift',E'Line',G'Lot#',I'Prod Date',J'Units',K'Pack Type',L'SKU'", 1);
QUERY('F Production Data Import'!A3:M,
"select E,D,G,I,J,K,L
where I = DATE '"&TEXT(C2, "yyyy-MM-dd")&"'", )},
"where Col3 is not null
order by Col3", 1)