Excludeing Unwanted Country in the Script level

[manishapanda1]

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

[dplr-rn]

you could use a where clause

e.g.

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

[dplr-rn]

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]

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);

[devarasu07]

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]

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]

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]

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]

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]

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)