topic Re: count set analysis in QlikView

count set analysis

nicolai_moller — Fri, 16 Aug 2013 13:04:56 GMT

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.

Re: count set analysis

tresB — Fri, 16 Aug 2013 13:15:54 GMT

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

Re: count set analysis

nicolai_moller — Fri, 16 Aug 2013 13:21:04 GMT

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

Re: count set analysis

tresB — Fri, 16 Aug 2013 13:22:09 GMT

Can you please share a sample app?

Re: count set analysis

nicolai_moller — Fri, 16 Aug 2013 13:39:19 GMT

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.

Re: count set analysis

tresB — Fri, 16 Aug 2013 13:55:41 GMT

PFA. I have made some changes in the script.

Is this what you want?

Re: count set analysis

nicolai_moller — Tue, 20 Aug 2013 07:31:22 GMT

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

Re: count set analysis

Tue, 20 Aug 2013 07:43:19 GMT

Hov hov lav selv dine lektier

Re: count set analysis

nicolai_moller — Tue, 20 Aug 2013 10:20:19 GMT

Nu skal du ikke være næsvis

Re: count set analysis

Kushal_Chawda — Tue, 20 Aug 2013 10:57:38 GMT

see the attached one

Re: count set analysis

Tue, 20 Aug 2013 11:01:12 GMT

Er du blevet certificeret? Hvorfor sidder i med qlikview? Kan du ikke lige give mig din mail?

Re: count set analysis

nicolai_moller — Tue, 20 Aug 2013 12:09:15 GMT

Jeg sidder på nmp@ankl.dk