Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

Use a mapping table in the WHERE condition

Hello All !

I would like to know if it's possible to use a mapping table (or conditions from another table) in the WHERE fonction.
Indeed, i load data from several sources and, for some values within, i don't want to load all.
I mean, i've several markets, and some have not so well-consolidated values, so i would like to remove them from the load during a specific time (after this time, data are quite better so i can use them...)

Does QV provide a way to process this ?

Thanks a lot !

6 Replies
Highlighted
Not applicable

Use a mapping table in the WHERE condition

Hello Franck,

Would an 'exists' test (with 'not' to negate it) in your where clause achieve what you need?

Regards,

Gordon

Highlighted
Not applicable

Use a mapping table in the WHERE condition

In fact, this is the kind of format i would use

CountrySales_Start_YearSales_Start_Month
USA200712
UK20082
FR200712
Canada20093

And, in the sales query, for instance, it would be something like that :

LOAD
....

FROM Sales

WHERE Country=$_reference(country) AND Year=$_reference(Year)....

I hope i'm clearer 😉

Highlighted
Champion III
Champion III

Use a mapping table in the WHERE condition

Franck,
this is exactly what Gordon says:
WHERE exists(Country) and exists(Year)
Country and Year must be loaded before usin it in exists(). As for your original question - you certainly can use mapping in conditions. Assuming you have a map of Countries 'CountryMap', the condition could be;
WHERE applymap('CountryMap', Country, 'NONE') <> 'NONE'

Highlighted
Not applicable

Use a mapping table in the WHERE condition

Thanks for your help, Michael.
Anyway, it seems your code won't work.

Let me re-explain what i would like:

I would like to use a table (Country - Year - Month), use it as a reference table
When QlikView loads the Sales, it will check in the Reference Table if the Country is in this list
1) It's in this list, so QlikView gets the Year and Month and only load MORE Recent Data than Ref_Table.Year & Ref_Table.Month
2) It's not in this list, so load everything....


Highlighted
Not applicable

Use a mapping table in the WHERE condition

Thanks for your help, Michael.
Anyway, it seems your code won't work.

Let me re-explain what i would like to achieve:
I would like to use a table (Country - Year - Month), use it as a reference table When QlikView loads the Sales, it will check in the Reference Table if the Country is in this list

1) It's in this list, so QlikView gets the Year and Month and only load MORE Recent Data than Ref_Table.Year & Ref_Table.Month

2) It's not in this list, so load everything....

Can anyone help me on this topic ?

Highlighted
Champion III
Champion III

Use a mapping table in the WHERE condition

Franck,
See attached. I'm not sure if it's the simpliest way, but it works the way you explained it.