Building dynamic WHERE clause

[Anonymous]

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	Country	State	Sales
2011	USA	CA	1000
2011	USA	TX	700
2011	Singapore		500

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

C_ID	C_Country	C_State
1	USA	CA
2	USA
3	Singapore

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

[#2]

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

[Anonymous]

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

[#4]

Indeed Anton, it would get complicated with more fields because you would need to keep creating variables etc.

Do let me know what you end up doing,

Regards, Erica

[#5]

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

[#6]

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

[Anonymous]

Thanks for your input Akshata,

I wasn't aware of the "alt" function before, now I know , and I can use in this scenario

Best regards,

Anton