conditional exclusion

[#1]

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........

[alexandros17]

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;

[sunny_talwar]

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

[#4]

thanks for the reply.

but its not working.

any other suggestions plssssssssssss

[sunny_talwar]

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

[maxgro]

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;

[MarcoWedel]

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

[jagan]

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.