topic Re: conditional exclude in QlikView

conditional exclude

Fri, 23 Oct 2015 16:41:53 GMT

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

Re: conditional exclude

maxgro — Fri, 23 Oct 2015 16:46:48 GMT

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;

Re: conditional exclude

sunny_talwar — Fri, 23 Oct 2015 16:47:37 GMT

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.

Re: conditional exclude

Fri, 23 Oct 2015 16:59:09 GMT

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

Re: conditional exclude

sunny_talwar — Fri, 23 Oct 2015 17:01:10 GMT

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

Re: conditional exclude

Fri, 23 Oct 2015 17:39:56 GMT

THANKS SUNNY

ITS WORKING NOW

Re: conditional exclude

sunny_talwar — Fri, 23 Oct 2015 17:40:56 GMT

Awesome