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

[#1]

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

[MayilVahanan]

Hi

Try like this

member:

load * inline

[Mem_key,Mem_name,memID

M201,'Sara White',1001

M201,'Sara White',1001

M201,'Sara White',1001

M201,'Sara White',1001

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'

or

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

M201,'Sara White',1001

M201,'Sara White',1001

M201,'Sara White',1001

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

[rwunderlich]

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

-Rob

[#4]

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.

[rwunderlich · Accepted solution]

Attached is possible solution.

-Rob

[#6]

Possible option

[#7]

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

[#8]

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.