Sunday, November 25, 2012

yogi_Query Data Conditionally To Compute FirstChance Or SecondChance Grades As Specified

                                          Google Spreadsheet   Post  #892

              Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Nov 25, 2012
user Juan Bozzo said:(http://productforums.google.com/forum/?zx=jnm1y5sz2w47#!category-topic/docs/spreadsheets/n13iR-0y5fU)
QUERY breaks under conditional data source
I`ve noticed that QUERY sometimes loads wrong cells when the data source (the range) of the query is not fixed. I've made an example sheet to show the issue; https://docs.google.com/a/uc.cl/spreadsheet/ccc?key=0Ar2-w83T2UgmdEpOMFc1eDV3cU5aOU5wcWZvaW1taGc#gid=3

To give a little context to the sheets: Imagine you are a teacher that want to publish some exam grades. You have corrected the exam and filled up a table with details of the points earned in every item. Then you publish that table, some students show you that have not been graded correcty. You fill other table with the new grades of only the people whose grades changed. Now you need to merge that data.

So you have four sheets. One that gives every student an id. Other two that have the grades and points of the firsts and second opportunities you graded, they are the almost sheet but with different data. And a fourth sheet that merges the data of the previous two sheets. The way the second sheet works is using a boolean per row that tells a query function if it has to load the row from the first data sheet or the second. When the bool is false, everything works ok, but if the boolean is true. The query function loads the data of the all previus rows. All the detail of how to reproduce this in the sheet itself.

I know this problem can be solved using other methods but is just an example to show the issue with QUERY.

---------------------------------------------------------------------------------------------------
I found that the QUERY function works if it is used correctly ... I have provided a solution to your problem in the following