topic Re: Building dynamic WHERE clause in QlikView

Building dynamic WHERE clause

Anonymous — Mon, 14 Nov 2011 02:43:59 GMT

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

Re: Building dynamic WHERE clause

Mon, 14 Nov 2011 09:44:30 GMT

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

Anonymous — Mon, 14 Nov 2011 14:29:29 GMT

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

Re: Building dynamic WHERE clause

Mon, 14 Nov 2011 14:36:13 GMT

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

Building dynamic WHERE clause

Tue, 15 Nov 2011 08:59:38 GMT

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

Fri, 09 Dec 2016 16:03:11 GMT

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

Re: Building dynamic WHERE clause

Anonymous — Fri, 09 Dec 2016 17:01:39 GMT

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