topic Re: Exluding data in script in QlikView

Exluding data in script

stekol61 — Wed, 02 Aug 2017 08:50:09 GMT

Hi!

I'm loading the dimentions below and want to present them in a report/graph.

When I select e.g. Country='S' I want to exclude the Hosts containg 'LAB'.

I also need to have the possibility to see the LAB-Hosts. E.g. by usiga a Dummy 'Country' ='LAB'

Not sure what's the best way to solve this,. In the scipt or in the report

Week	Country	HostName
31	F	PM1
31	F	PM2
31	F	LAB
31	N	PM3
31	N	PM4
31	N	LAB2
31	S	FRE2P
31	S	HY2P
31	S	LAB3P
31	S	LAB4P

Re: Exluding data in script

balar025 — Wed, 02 Aug 2017 08:54:49 GMT

Hi Stefan,

Can you please provide your expected output?

-Ravi

Re: Exluding data in script

Anil_Babu_Samineni — Wed, 02 Aug 2017 08:55:40 GMT

If you select "S" then your report should like below? or please provide sample output

Week Country HostName

31 S FRE2P

31 S HY2P

Re: Exluding data in script

effinty2112 — Wed, 02 Aug 2017 08:58:08 GMT

Hi Stefan,

For the first question try:

{$-<Country = {'S'}, HostName = {'LAB*'}>}

as a set modifier in your expression.

I don't quite understand the second problem, can you explain a bit further.

cheers

Andrew

Re: Exluding data in script

balar025 — Wed, 02 Aug 2017 09:01:35 GMT

as i understand, can you try below script?

Data:

Load * Inline

[

Week, Country, HostName

31, F, PM1

31, F, PM2

31, F, LAB

31, N, PM3

31, N, PM4

31,N, LAB2

31, S, FRE2P

31, S, HY2P

31, S, LAB3P

31, S, LAB4P

];

//QUALIFY *;

Data_New:

Load *,1 as Flag

Resident Data

where not (Country='S' and WildMatch(HostName,'LAB*'));

Concatenate

Load Week, 'LAB' as Country, HostName ,2 as Flag

Resident Data

where (Country='S' and WildMatch(HostName,'LAB*'));

Drop table Data;

Output:

F	LAB	31
F	PM1	31
F	PM2	31
LAB	LAB3P	31
LAB	LAB4P	31
N	LAB2	31
N	PM3	31
N	PM4	31
S	FRE2P	31
S	HY2P	31

Re: Exluding data in script

stekol61 — Wed, 02 Aug 2017 09:07:36 GMT

Hi!

If I select "S" the report should look like this:

Week Country HostName

31 S FRE2P

31 S HY2P

If I select the "Dummy" Country 'LAB' I want to see all hosts containg the name 'LAB'

Whare to i use the set modifier in my?expression

Re: Exluding data in script

PrashantSangle — Wed, 02 Aug 2017 09:14:17 GMT

What about Country 'N'?? LAB2 should display in country N???

well You can create new field in script like

if(Country='S' and wildmatch(HostName,'LAB*'),'LAB',Country) as newCountry

Use newCountry field in front end instead of Country.

Regards,

Prashant

Re: Exluding data in script

effinty2112 — Wed, 02 Aug 2017 09:16:34 GMT

Hi Stefan,

I think there are better ways to see all your hosts with name beginning LAB.

Data:

Load

If(WildMatch(HostName,'LAB*'),'LAB','Not LAB') as IsItLAB?;

LOAD * INLINE [

Week, Country, HostName

31, F, PM1

31, F, PM2

31, F, LAB

31, N, PM3

31, N, PM4

31, N, LAB2

31, S, FRE2P

31, S, HY2P

31, S, LAB3P

31, S, LAB4P

];

Or why not just a searchbox on the field HostName?

cheers

Andrew

Re: Exluding data in script

bharani8 — Wed, 02 Aug 2017 09:21:31 GMT

Here you go... Let me know this is what u were looking for?

Re: Exluding data in script

balar025 — Wed, 02 Aug 2017 09:25:40 GMT

is it not helpful?

Data:

Load * Inline

[

Week, Country, HostName

31, F, PM1

31, F, PM2

31, F, LAB

31, N, PM3

31, N, PM4

31,N, LAB2

31, S, FRE2P

31, S, HY2P

31, S, LAB3P

31, S, LAB4P

];

//QUALIFY *;

Data_New:

Load *,1 as Flag

Resident Data

where not (Country='S' and WildMatch(HostName,'LAB*'));

Concatenate

Load Week, 'LAB' as Country, HostName ,2 as Flag

Resident Data

where (Country='S' and WildMatch(HostName,'LAB*'));

Drop table Data;

Re: Exluding data in script

Anonymous — Wed, 02 Aug 2017 09:35:50 GMT

Hi Stefan,

Can you please try below code in Script editor:

Test 3:

Load Week,

If(WildMatch(HostName,'LAB*','LAB',Country) as Country,

HostName;

Load * Inline

[

Week, Country, HostName

31, F, PM1

31, F, PM2

31, F, LAB

31, N, PM3

31, N, PM4

31,N, LAB2

31, S, FRE2P

31, S, HY2P

31, S, LAB3P

31, S, LAB4P

];

Output :

Re: Exluding data in script

Anonymous — Wed, 02 Aug 2017 09:37:08 GMT

Syntax mistake.. Please find correct one as below:

Test 3:

Load Week,

If(WildMatch(HostName,'LAB*'),'LAB',Country) as Country,

HostName;

Load * Inline

[

Week, Country, HostName

31, F, PM1

31, F, PM2

31, F, LAB

31, N, PM3

31, N, PM4

31,N, LAB2

31, S, FRE2P

31, S, HY2P

31, S, LAB3P

31, S, LAB4P

];

Re: Exluding data in script

senpradip007 — Wed, 02 Aug 2017 09:45:56 GMT

Have look at this sample app. Hope it will help.

Re: Exluding data in script

bharani8 — Wed, 02 Aug 2017 10:00:02 GMT

Here you go..

Re: Exluding data in script

stekol61 — Wed, 02 Aug 2017 10:10:09 GMT

Thank you Bharani.

This solved my problem

/Stefan

Re: Exluding data in script

bharani8 — Wed, 02 Aug 2017 10:13:12 GMT

Wc! Please mark it as Correct so it will be useful for others while searching for this

Re: Exluding data in script

stekol61 — Wed, 02 Aug 2017 10:21:15 GMT

Done

/Stefan

Re: Exluding data in script

bharani8 — Wed, 02 Aug 2017 10:24:31 GMT

Haha.. You hav marked the wrong one as Correct

Re: Exluding data in script

stekol61 — Wed, 02 Aug 2017 10:26:38 GMT

Oops.

Should be OK now

Re: Exluding data in script

Anonymous — Wed, 02 Aug 2017 10:41:03 GMT

Hi Stefan,

You have asked for a solution like below:

Hi!

If I select "S" the report should look like this:

Week Country HostName

31 S FRE2P

31 S HY2P

If I select the "Dummy" Country 'LAB' I want to see all hosts containg the name 'LAB'

Whare to i use the set modifier in my?expression

But the correct answer you marked doesnt have solution for your second question. Am i right?