6 Replies Latest reply: Dec 9, 2016 12:01 PM by Anton Wibowo RSS

    Building dynamic WHERE clause

    Anton Wibowo



      I'm hoping someone can help me pointing into right direction. I'm trying to build a Table from dataset (stored in "Data" table), of which its criteria are stored in another table.


      The main data is stored in "Data" table in QV looks like:



      "CriteriaTable", which the criteria are stored, looks like:




      On the front-end, when user click on an "ID", I would like to populate the chart using month as dimension and total sales as expression based on the criteria specified for particular ID. To approach this, I tried to build a new table that contains the collection of sub dataset which already been filtered for particular "ID".


      SubSetCollection:    // Building empty table first to store the "new" dataset 
      LOAD * INLINE [SetID, SetYear, SetCountry, SetState, SetSales];
      // Reset the variables to store criteria for every single "ID"
      LET vID= '' ;
      LET vCountry= '' ;
      LET vState= '' ;
      FOR RecNumber = 0 TO (NoOfRows('CriteriaTable')-1)    // loop through the criteria table, 1 row at a time 
          LET vID = trim(PEEK('C_ID', '$(RecNumber)', 'CriteriaTable'));
          LET vCountry = trim(PEEK('C_Country', '$(RecNumber)', 'CriteriaTable'));
          LET vState = trim(PEEK('C_State', '$(RecNumber)', 'CriteriaTable'));
          Concatenate (SubSetCollection)         // Build dataset based on criteria specific to each "ID" 
              '$(vID)' as SetID
           , num(Year) as SetYear
           , Country as SetCountry
           , State as SetState
           , num(Sales) as SetSales
           RESIDENT Data
           WHERE Country = '$(vCountry)'
                     and State = '$(vState)';


      Now, when the criterion field is blank or null, I don't want to use it/include it as part of the criteria for this dataset.

      For example, when user click ID=2, it should filter data for Country='USA' as a whole, and total Sales Amount for year 2011 should be $1700.


      My problem with the current WHERE clause above is, it works as if I tried to find Country='USA', and State='' or State=null().

      Since there's no NULL value in State fields anywhere in the "Data" table , it returns nothing (instead of using Country='USA' as the only criterion).


      I've tried to do some filtering whether the variable is null, e.g.: WHERE ( Country = '$(vCountry)' or isnull($(vCountry) )

      , which is similar to that of SQL listed below:

      Select * from Data

      Where ( @Country is null or Country = @Country)

               and (@State is null or State = @State)



      However, it failed to load, and the following error is generated


      Field not found - <USA>

      Concatenate (SubSetCollection)        


              '1' as SetID

           , num(Year) as SetYear

           , Country as SetCountry

           , State as SetState

           , num(Sales) as SetSales

           RESIDENT Data

           WHERE ( Country = 'USA' or isnull(USA) )

                     and ( State = 'CA' or isnull(CA)    )




      Any suggestion will be appreciated. I'm also open to suggestion of using alternative methods as I understand there could be various ways to achive the same result.


      Thank you




        • Re: Building dynamic WHERE clause
          Hi AWQVUser.
          An alternative would be to set the blank C_State values to "*" and pass that to a variable instead. You could then use set analysis in your chart to search for all values that are there, as if you were searching for "*" normally.
          You would not need to rename all the fields in the table with the C_ prefix, or require the second part of the script. Only the State part would require the suffix.
          So the criteria table will have the fields ID, Country, C_State. Your document variable will reflect C_State with somethine like =only(C_State).
          The chart would then comprise the month as dimension, and the following expression for sum of sales:
          =sum({<State={$(vState) }>} Sales)
          This searches for all states that match the search string "*" ie, all of them.
          Good luck!
            • Re: Building dynamic WHERE clause
              Anton Wibowo



              Thank you for your prompt response. I can see it working for this scenario.

              It might be tricky if I were to have more fields (I think).


              Let me try it out first, by modifiying my table to expand Data & CriteriaTable to have more fields, such as Status (which values are either Yes or No). Then, On CriteriaTable, I'll add new ID=4 that will search for Status='Yes' only regardless what countries/states. I'll post it here whether I can make it work or not.


              Again, thank you



            • Building dynamic WHERE clause

              you can add the below expression in your chart to get total sales according to id:




              you get the total sales based on id.


              try it.



              • Re: Building dynamic WHERE clause
                akshata tare

                It is a really old question and I am sure an alternate was found. I would have tried the following 


                          alt('$(vCountry)','default value like USA')