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" 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>
'1' as SetID
, num(Year) as SetYear
, Country as SetCountry
, State as SetState
, num(Sales) as SetSales
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.
DynamicWHERE - Copy.qvw 160.2 K