Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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
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')
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