3

I am creating a spreadsheet for my work place that will find drivers for the LED's we use but I've run into a slight problem. i pull in the data from another sheet to thin it out, i then have a sheet for formulas, one for the form, and the data on its own sheet. for some reason, i do a calculation on one sheet, then use the query function to return that result on another sheet, but the result sometimes misses data.

I am using this formula to determine how many LED's can be attached to the driver:

=IF(A4="","",IF(U4="Y",If(K4>1,IF(round(O4)=round(P4),P4&" per Channel",O4&"-"&P4&" per Channel"),IF(round(O4)=round(P4),P4,O4)),IF(U4="Min+1",If(K4>1,IF(round(O4+1)=round(P4),P4&" per Channel",(O4+1)&"-"&P4&" per Channel"),IF(round(O4+1)=round(P4),P4,(O4+1)&"-"&P4)),IF(U4="Max-1",If(K4>1,IF(round(O4)=round(P4-1),O4&" per Channel",O4&"-"&(P4-1)&" per Channel"),IF(round(O4)=round(P4-1),P4,O4&"-"&(P4-1)))))))

I then use this function to display a list of all the drivers that will work, but the result of the previous formula, even though it displays on the formulas sheet, will not display on the forms sheet.

=IFERROR(if($C$3="Y",if(BFFormulas!R1="CC",query(BFFormulas!A3:Z900,"select A,B,D,H,I,K,Q,L where A != '' and J='Y' and N = 'Y' order by D ",1),if(BFFormulas!R1="CV",query(BFFormulas!A3:Z900,"select A,B,D,H,I,K,Q,L where V > "&BFFormulas!T1*D3&" and A != '' and J='Y' order by D ",1),"Missing Fixture Information")),if(BFFormulas!R1="CC",query(BFFormulas!A3:Z900,"select A,B,D,H,I,K,Q,L where A != '' and N = 'Y' order by D ",1),if(BFFormulas!R1="CV",query(BFFormulas!A3:Z900,"select A,B,D,H,I,K,Q,L where V > "&BFFormulas!T1*D3&" and A != '' order by D ",1),"Missing Fixture Information"))),"Could Not Find Fixture From This Source.")

Can anyone tell me why this is happening?

Image of what happening: https://drive.google.com/file/d/0Bw6otjO0spyBd2NXYWhuQm1QbnM/view?usp=sharing

Aaron Effinger
  • 43
  • 1
  • 1
  • 3

2 Answers2

2

QUERY will convert columns with mixed data types into one data type. If the data is mostly numerical values, then text strings (eg 1-2 in your data) will be converted to blank cells.

A workaround is to convert everything to text strings (if the consequences of that are acceptable to you). Note the &"" appended to each range in the QUERY functions.

=ArrayFormula(IFERROR(if($C$3="Y",if(BFFormulas!R1="CC",query(BFFormulas!A3:Z900&"","select A,B,D,H,I,K,Q,L where A != '' and J='Y' and N = 'Y' order by D ",1),if(BFFormulas!R1="CV",query(BFFormulas!A3:Z900&"","select A,B,D,H,I,K,Q,L where V > "&BFFormulas!T1*D3&" and A != '' and J='Y' order by D ",1),"Missing Fixture Information")),if(BFFormulas!R1="CC",query(BFFormulas!A3:Z900&"","select A,B,D,H,I,K,Q,L where A != '' and N = 'Y' order by D ",1),if(BFFormulas!R1="CV",query(BFFormulas!A3:Z900&"","select A,B,D,H,I,K,Q,L where V > "&BFFormulas!T1*D3&" and A != '' order by D ",1),"Missing Fixture Information"))),"Could Not Find Fixture From This Source."))

AdamL
  • 23,691
  • 6
  • 68
  • 59
  • Thanks, that fixed it. I just added &" " to the end of all the 1-2 or 2-4 results and now it displays correctly. thank you for the help! – Aaron Effinger Apr 21 '15 at 02:13
2

Another good solution is to explicitly convert the mixed data type columns to strings with TO_TEXT(). TO_TEXT() won't work inside the QUERY() function on it's own, but since you're already using ARRAYFORMULA(), wrapping the query's range in TO_TEXT() might help solve the problem.

TO_TEXT() will get rid of the header values in the range, so instead of QUERY(A:B, "SELECT B WHERE A = 'this one'"), you'd need to use Virtual Column headers Col1 and Col2. ARRAYFORUMLA(QUERY(TO_TEXT(A:B), "SELECT Col2 WHERE Col1 = 'this one'"))

Source: https://infoinspired.com/google-docs/spreadsheet/mixed-data-type-issue-in-query/

Amanda
  • 314
  • 3
  • 12