topic Aggr function count distinct in a script in QlikView

Aggr function count distinct in a script

femidadzie — Mon, 19 Nov 2018 18:46:26 GMT

Dear Community,

iam facing a problem here and i couldn't find a proper solution in the community, so i hoped that maybe someone could help me.

Iam trying to create a field in the script which counts the distinct material for each order with the status 'mp'.

Iam not gettin the 'distinct' charackter into the script.

I will add a file, including the wished result in hope this helps a bit.

I would be really thankful if someone could help me.

Many thanks in advance.

/////////////////////////////////////////////////////////////

Basis:
LOAD
    OrderID,
    MaterialID,
    MaterialStatus,
    If(MaterialStatus='mp',1,0) as Statusmp
FROM Desktop;

Qualify ;
Unqualify OrderID;

temp:
Load
    OrderID,
    MaterialID,
    Sum(Statusmp) as Numbermp

Resident Basis
Group By [OrderID], [MaterialID];

////////////////////////////////////////////////////////////////////

Re: Aggr function count distinct in a script

sunny_talwar — Mon, 19 Nov 2018 19:39:26 GMT

Based on the data provided what do you expect to see as your final output?

Re: Aggr function count distinct in a script

lironbaram — Mon, 19 Nov 2018 19:53:03 GMT

i would do something like this :

Basis:
LOAD
    OrderID,
    MaterialID,
    MaterialStatus

FROM Desktop;

Basis2:

load *,
If(MaterialStatus='mp' and previous(MaterialID)<>MaterialID ,1,0) as Statusmp
resident Basis

order by OrderID,MaterialID;

drop table Basis;

temp:
Load
OrderID,
Sum(Statusmp) as Numbermp

Resident Basis2
Group By [OrderID];

Re: Aggr function count distinct in a script

zoltann14 — Mon, 19 Nov 2018 19:56:00 GMT

You can just add DISTINCT to your Basis table load and then sum everything Grouping by OrderID:
Basis:
LOAD distinct
OrderID,
MaterialID,
MaterialStatus,
IF(MaterialStatus='mp',1,0) as Statusmp
FROM Desktop;

temp:
LOad OrderID,
SUM(Statusmp) as Numbermp
Resident Basis
Group by OrderID;

Re: Aggr function count distinct in a script

femidadzie — Mon, 19 Nov 2018 21:30:59 GMT

Hello Stalwar1

I would like to see based on the basis table,

Basis
OrderID	MaterialID	MaterialStatus
A	1	wo
A	1	mp
A	2	mp
A	2	mp
B	1	wo
B	1	wo
B	2	wo
B	2	wo
B	3	mp
C	2	wo
C	3	wo
C	3	wo
D	2	mp
D	2	wo
D	3	mp
D	3	mp
D	1	mp
D	1	wo

the following result:

OrderID	NumberOfMP
A	2
B	1
C	0
D	3

Re: Aggr function count distinct in a script

sunny_talwar — Mon, 19 Nov 2018 21:35:26 GMT

Try this

Basis:
LOAD
    OrderID,
    MaterialID,
    MaterialStatus
FROM Desktop;

Qualify ;
Unqualify OrderID;

temp:
 Load OrderID,
    Count(DISTINCT If(MaterialStatus = 'mp', MaterialID)) as Numbermp
Resident Basis
Group By [OrderID];

Re: Aggr function count distinct in a script

femidadzie — Tue, 20 Nov 2018 18:04:03 GMT

Thank´you very much that worked.
BS
Femi

Re: Aggr function count distinct in a script

femidadzie — Tue, 20 Nov 2018 18:05:24 GMT

Thanks!

Re: Aggr function count distinct in a script

femidadzie — Tue, 20 Nov 2018 18:06:10 GMT

Thank you!