topic Re: conditional exclusion in QlikView

conditional exclusion

Tue, 26 May 2015 12:40:49 GMT

Hi experts,

I have a qvd which has

Sales:

Load CODE,

Customer,

amount

from (.qvd);

there are 195 codes each having more than 200 customers.

I have another table named exclusion with some code and customer as below

CODE CUSTOMER

001 L1257

001 a2538

001 e8596

002 i5869

002 e5686

004 j9688

006 m4521

and so on...

the sales table should look into the exclusion table and the sales should exclude the particular customer only in the particular branch as mentioned in the exclusion table.

For example, in the exclusion table, we have the first record as,

CODE CUSTOMER

001 L1257

the sales should exclude this customer L1257 for code 001 alone.

the same customer may exists in different code and they shouldn't be excluded.

Either of this can be done in script or expression.???

How can i do this.

Please give suggestions.

Its veryyyyyyyyyy urgentttttttttttt........

Re: conditional exclusion

alexandros17 — Tue, 26 May 2015 12:49:34 GMT

A simply way is:

Sales_tmp:

Load CODE,

Customer,

amount

from (.qvd);

left join

load

CODE, Customer , '1' as to_be_excluded resident Exclusion_table

Sales: noconcatenate

load * resident Sales_tmp where to_be_excluded <> '1';

drop table Sales_tmp;

Re: conditional exclusion

sunny_talwar — Tue, 26 May 2015 12:53:43 GMT

You can do like this:

Exclusion:

LOAD *,

CODE&CUSTOMER as Check;

LOAD * Inline [

CODE, CUSTOMER

001, L1257

001, a2538

001, e8596

002, i5869

002, e5686

004, j9688

006, m4521

];

Sales:

Load CODE,

Customer,

amount

from (.qvd)
Where not Exists(Check, CODE&Customer);

HTH

Best,

Sunny

Re: conditional exclusion

Tue, 26 May 2015 16:06:16 GMT

thanks for the reply.

but its not working.

any other suggestions plssssssssssss

Re: conditional exclusion

sunny_talwar — Tue, 26 May 2015 16:13:58 GMT

Both solutions did not work for you??

Would you be able to share a sample rows of data from Sales table to test it?

Best,

Sunny

Re: conditional exclusion

maxgro — Tue, 26 May 2015 19:37:13 GMT

I think sunindia answer should work

Exclusion:

LOAD

CODE & '-' & CUSTOMER as Check;

LOAD * Inline [

CODE, CUSTOMER

004, j9688

006, m4521

];

Sales:

Load * inline [

CODE, CUSTOMER, AMOUNT

001, L1257, 100

001, a2538, 100

001, e8596, 100

002, i5869, 100

002, e5686, 100

004, j9688, 100

006, m4521, 100

]

Where not Exists(Check, CODE & '-' & CUSTOMER);

DROP Table Exclusion;

Re: conditional exclusion

MarcoWedel — Tue, 26 May 2015 21:57:12 GMT

Hi,

one possible solution could be to add a flag for excluded Code/Customer combinations and to disregard corresponding amounts in the sum() calculation using a set expression:

tabSales:

LOAD Num(Ceil(Rand()*5),'000') as CODE,

Chr(64+Ceil(Rand()*2))&Num(Ceil(Rand()*5),'0000') as Customer,

Money(Ceil(Rand()*100)) as amount

AutoGenerate 30;

tabExcluded:

LOAD Num(Ceil(Rand()*5),'000') as CODE,

Chr(64+Ceil(Rand()*2))&Num(Ceil(Rand()*5),'0000') as Customer,

1 as excluded

AutoGenerate 10;

hope this helps

regards

Marco

Re: conditional exclusion

jagan — Wed, 27 May 2015 02:27:01 GMT

Hi,

Try this script

ExclusionCustomers:

LOAD

Code & '_' & Customer AS Key

FROM DataSource;

Sales:

LOAD

FROM SalesDataSource

WHERE NOT Exists(Key, Code & '_' & Customer);

DROP TABLE ExclusionCustomers;

Regards,

Jagan.