Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
Not applicable
Author

Hello Franck,

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

Regards,

Gordon

Not applicable
Author

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 😉

Anonymous
Not applicable
Author

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'

Not applicable
Author

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....


Not applicable
Author

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 ?

Anonymous
Not applicable
Author

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