topic Re: How to exclude members those are...Script Question? in QlikView

How to exclude members those are...Script Question?

Mon, 19 Nov 2012 22:24:01 GMT

Hi All-Here I have attched a sample document.My objective is to exclude those menbers from script who are on Diabetics drugs.Being that said,in the final table I donot want to see SARA WHITE and TOM RICHMOND.

In the actual environment,I have 60-70 millions records in the Fact table.Please suggest all the possible options considering optimization.

Thanks-Pooja

Re: How to exclude members those are...Script Question?

MayilVahanan — Mon, 19 Nov 2012 22:31:17 GMT

Try like this

member:

load * inline

[Mem_key,Mem_name,memID

M201,'Sara White',1001

M202,'Dan Hill',1002

M203,'Kelly Schmidt',1003

M204,'Tom Richmond',1004

M205,'Adam Ristow',1005]where not Match(Mem_name,'Tom Richmond','Sara White');

this doesn't load the entire row of 'Tom Richmond','Sara White'

member:

load *,if(not Match(Mem_name,'Tom Richmond','Sara White'),Mem_name) as Mem inline

[Mem_key,Mem_name,memID

M201,'Sara White',1001

M202,'Dan Hill',1002

M203,'Kelly Schmidt',1003

M204,'Tom Richmond',1004

M205,'Adam Ristow',1005];

to remove the 'Tom Richmond','Sara White in the Mem_name alone ..

hope it helps

Re: How to exclude members those are...Script Question?

rwunderlich — Mon, 19 Nov 2012 23:24:18 GMT

Do you need to load members that have no facts, ie M205,'Adam Ristow'?

-Rob

Re: How to exclude members those are...Script Question?

Tue, 20 Nov 2012 00:05:53 GMT

Yes Rob-I need to bring that record into my final script table,though I am not going to display that one in the sheet object.

Re: How to exclude members those are...Script Question?

rwunderlich — Tue, 20 Nov 2012 05:12:17 GMT

Attached is possible solution.

-Rob

Re: How to exclude members those are...Script Question?

Tue, 20 Nov 2012 07:05:34 GMT

Possible option

Re: How to exclude members those are...Script Question?

Tue, 20 Nov 2012 23:17:52 GMT

Thanks Rob..I was thinking something like this But was stuck with where exists.Thanks again for your help!

Re: How to exclude members those are...Script Question?

Tue, 20 Nov 2012 23:20:07 GMT

Thanks for your reply.I donot think it would be a good idea to bring everything to a single table where fact has more than 70 M records.left join would take time.