count set analysis

[nicolai_moller]

Hi

I'm trying to write an expression (in an pivot table) with a set analysis that counts the number of goods that a department has received.

At the moment it only shows the department where the good is currently located, which in some cases is different from the receiving department.

The problem is that they have different IDs, so the column with 'Goods received Department' has one name but a different one in 'Goods Department, even though it is the same department. The two fields are mapped together in QlikView though.

I want the 'Goods Deparment' to count the number of goods it has received, even though some goods may not exist there anymore. But the pivot table should only show the 'Goods Deparment' and not 'Goods Received Department'.

Goods Department	Goods Received Department	Goods ID
A	P1	123
B	P1	124
B	P1	125
C	P2	126
C	P2	127
D	P3	128

So if P1 = A, then A should count 3 (instead of 1 in the table above) and if P2 = D, then D should count 2.

Thanks.

[tresesco]

It would be better if you take the solution to the script. Create two different tables like:

MasterDept:

Load

          [Goods Department],

          Location,

          ...

From <>;

Transaction:

Load

          [Goods Received Department],

          ApplyMap(....)  as [Goods Department],  // for mapping like P1=A, P2=D

          [Goods ID]

From <>

[nicolai_moller]

They are already mapped, but I dont know how to display it correctly in a pivot table.

[tresesco]

Can you please share a sample app?

[nicolai_moller]

If you select Goods ID 123, 124 and 125 in the attached document, all 3 goods were received in department P1.

One of the goods is located in department A the other 2 in department B.

In the table called link, I have specified how the receiving department and the current department are mapped together.

So P1 = A,P2= C, P3 = D.

And if P1 has received 3 goods and P1 is also = A, then I want 'Goods deparment' A to display 3 goods. Right now the pivot table only show 1 good in A.

[tresesco]

PFA. I have made some changes in the script.

Is this what you want?

[nicolai_moller]

Would it be possible to do without changing the script? In an expression?

[#8]

Hov hov lav selv dine lektier

[nicolai_moller]

Nu skal du ikke være næsvis

[Kushal_Chawda · Accepted solution]

see the attached one