Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On Demand Webinar: See Why Thousands of QlikView Users Have Switched to Qlik Sense. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
awqvuser
Creator
Creator

Building dynamic WHERE clause

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

6 Replies
Not applicable

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
awqvuser
Creator
Creator

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

Not applicable

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

Not applicable

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

Not applicable

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

awqvuser
Creator
Creator

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