Use a mapping table in the WHERE condition

[#1]

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 !

[#2]

Hello Franck,

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

Regards,

Gordon

[#3]

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

Country	Sales_Start_Year	Sales_Start_Month
USA	2007	12
UK	2008	2
FR	2007	12
Canada	2009	3

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]

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'

[#5]

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

[#6]

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]

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