Search code examples
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:

  • The source data is not blank for these two cells.
  • When I change the 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).
  • All values in the source data (both before and after importing) are formatted consistently, except for header rows.
  • Separating the queries to stand alone, this issue for the problematic sheet remains but the values are not blank here(!!), though they are justified incorrectly and seem to be the wrong format, ie the date is not perceived as a date.

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:

  • Reformatting data types (no change)
  • Isolating query functions ("problematic" data is not blank in this arrangement)
  • Shifting imported data to exclude "problematic" row of data ("problematic" row shifted to a new one that wasn't problematic before)

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)