google-sheetsimportunion

Google Sheets query is returning a couple blank cells even though they are not blank in the source data


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:

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:


Solution

  • 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)