topic Use a mapping table in the WHERE condition in QlikView

Use a mapping table in the WHERE condition

Mon, 01 Jun 2009 16:32:31 GMT

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 !

Use a mapping table in the WHERE condition

Mon, 01 Jun 2009 18:47:36 GMT

Hello Franck,

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

Regards,

Gordon

Use a mapping table in the WHERE condition

Mon, 01 Jun 2009 18:53:15 GMT

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 😉

Use a mapping table in the WHERE condition

Anonymous — Mon, 01 Jun 2009 23:46:28 GMT

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'

Use a mapping table in the WHERE condition

Tue, 02 Jun 2009 19:43:20 GMT

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

Use a mapping table in the WHERE condition

Tue, 02 Jun 2009 19:49:12 GMT

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 ?

Use a mapping table in the WHERE condition

Anonymous — Tue, 02 Jun 2009 22:33:05 GMT

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