conditional exclude

[#1]

Hi Experts,

i have the following tables

table1:

Load Branch,

        customer

from table1.qvd;

table2:

Load Branch,

         customer,

         Date,

         Amount

from table2.qvd;

table1 has customers with allocated branch.

table2 has all the branch, all customer sales.

i want to pick up the customer sales on their allocated branch alone and not on other branches.

for example:

table1 has customers with allocated branches as:

branch        customer

100             a1678

150             e4567

200             c987

....etc

but the sales of a particular customer might have done in some other branches as well other than allocated branches.

ex: customer a1678 is allocated to branch 100.

but the sales of this customer has done in branch 100 , branch 150, branch 200 also.

i want to pick up this customer sales only in their own branch.

how can i do that.

please help

[maxgro · Accepted solution]

in script

table1:

Load

     Branch,

     customer,

     Branch & '|' & customer as Allocated

from table1.qvd

;

table2:

Load Branch,

         customer,

         Date,

         Amount

from table2.qvd

where exists (Allocated, Branch & '|' & customer)

;

drop field Allocated;

[sunny_talwar]

May be something like this:

table1:

Mapping

LOAD customer,

          Branch,

from table1.qvd;

table2:

LOAD *,

          If(Branch = [Allocated Branch], 1, 0) as Flag;

Load Branch,

        ApplyMap('table1', customer) as [Allocated Branch]

        customer,

        Date,

        Amount

from table2.qvd;

Now use [Allocated Branch] where you wanted to use the allocated branch for sales.

[#4]

hi sunny,

both the table has same field names.

table1:

Load Branch,

        customer

from table1.qvd;

table2:

Load Branch,

         customer,

         Date,

         Amount

from table2.qvd;

how can i do with this. pls help

[sunny_talwar]

It doesn't matter if they are the same name. Both my approach and maxgro‌ solution should get you to what you are looking for.

May be if you can share few rows of data in a excel file, we might be able to show you how it can be done

[#6]

THANKS SUNNY

ITS WORKING NOW

[sunny_talwar]

Awesome