Solved: Excludeing Unwanted Country in the Script level - Qlik Community

manishapanda1 · ‎2019-08-16

Hi Friends ,

I have a scenario where my Country source table is a Excel file .in the Country column I have 100 country but user don't want few countries (eg: Aus,Uk,Us…...etc) approx 20 country shouldn't be there in the Dashboard.

can some one please help me to achieve this.

Thanks,

Manisha Panda

paulselousyoriz · ‎2019-08-16

If the countries to be excluded may change quite often , it might be a good option to include these countries in a seperate Excel file. So that when this list changes, you do not have to amend the script but just amend this Excel file. Here is an example code below:

"CountriesToExclude:
Load
Country As Country_To_Exclude
FROM [lib://Community\Countries_To_Exclude.xlsx]
(ooxml, embedded labels, table is Blad1);

Countries:
Load
Country
FROM [lib://Community\CountryList.xlsx]
(ooxml, embedded labels, table is Blad1)
Where Not Exists(Country_To_Exclude,Country);

Drop table CountriesToExclude;"

Good luck,

Paul

View solution in original post

dplr-rn · ‎2019-08-16

you could use a where clause

e.g.

where Match(StoreName,'IgnCountry1','IgnCountry2')=0

dplr-rn · ‎2019-08-16

or use not exists e.g. below

dummy:
load * Inline [
Storetemp
Store3
Store4
]
;
LOAD Store,
Customer,
Receipt_Date,
[Money in suspense],
StoreName
FROM
[C:\Users\Dilip-R\Downloads\Community.xlsx]
(ooxml, embedded labels, table is Sheet1)
where //Match(StoreName,'Store3','Store4')=0
not exists(Storetemp,StoreName)
;
drop table dummy;

jonathandienst · ‎2019-08-16

A join of this pattern is one way:

FactTable:
LOAD Country
FROM ... (XL, database, inline etc list of required countries);

Inner Join (FactTable)
LOAD Country,
	...
	...
	...
FROM ... (your fact table source);

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

devarasu07 · ‎2019-08-16

Hi,

Try to use Left or right Keep based on your Fact/master and also you use exists function.

Method 1:

CountryFilter:
LOAD * INLINE [
Country
Macau
Taiwan
];

LOAD * INLINE [
Country, Sales
India, 100
Pakistan, 200
Singapore, 300
Malaysia, 400
Thailand, 500
Indonesia, 600
Taiwan, 700
Macau, 800
Hong Kong, 900
] where Exists (Country);

method 2:

Fact:
LOAD * INLINE [
Country, Sales
India, 100
Pakistan, 200
Singapore, 300
Malaysia, 400
Thailand, 500
Indonesia, 600
Taiwan, 700
Macau, 800
Hong Kong, 900
];

right keep (Fact)
LOAD * INLINE [
Country
Macau
Taiwan
];

Note: Keep function much optimized than where Claus filter

Hope this helpful to you 🙂

Regards,

Deva

manishapanda1 · ‎2019-08-16

Thanks for your reply, but this will not solve the problem . The Country column I am taking from the Excel , I need some way like the way we do in SQL where condition (eg : where country not in('AUS','US',...…) .

Thanks,

Manisha P

devarasu07 · ‎2019-08-16

Fact:
LOAD * INLINE [
Country, Sales
India, 100
Pakistan, 200
Singapore, 300
Malaysia, 400
Thailand, 500
Indonesia, 600
Taiwan, 700
Macau, 800
Hong Kong, 900
] where not WildMatch(Country,'Taiwan','Macau');

refer to the below article which is very good

https://www.learnallbi.com/using-in-clause-in-qlikview/

manishapanda1 · ‎2019-08-16

Thanks for your reply. But I guess we can't apply where cond in excel . I need to filter the data while importing from excel

Thanks

Manisha P

devarasu07 · ‎2019-08-16

Nope, you can apply where clause while loading your excel data into Qlik

Fact:
LOAD Country,
Sales
FROM
[C:\Users\devarasu.rasavelu\Desktop\CountrySales.xlsx]
(ooxml, embedded labels, table is Sheet1) where not WildMatch(Country,'Taiwan','Macau');

dplr-rn · ‎2019-08-16

read the answers above clearly. there at least a few which should work for you.
there s 1 with a simple where condition. another one couple which makes it more flxible by putting the exclusion list into another excel/inline table and use exists (mine) or inner join (Jontydkpi's)