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

    Building dynamic WHERE clause

    Anton Wibowo

      Folks,

       

      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:

      Year
      CountryStateSales
      2011USACA1000
      2011USATX700
      2011Singapore
      500

       

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

       

      C_IDC_CountryC_State
      1USACA
      2USA
      3Singapore

       

      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" 
          LOAD
              '$(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)';
      NEXT;
      

       

      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)        

          LOAD

              '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

       

       

      Anton

        • 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!
          Erica
            • Re: Building dynamic WHERE clause
              Anton Wibowo

              Erica,

               

              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

               

              Anton

            • Building dynamic WHERE clause

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

               

              aggr(sum(totalsales),id);

               

              you get the total sales based on id.

               

              try it.

               

              regards

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