Aggr function count distinct in a script

[femidadzie]

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

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

[sunny_talwar]

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

[lironbaram]

hi 

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

[zoltann14]

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;

[femidadzie]

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

[sunny_talwar · Accepted solution]

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

[femidadzie]

Thank´you very much that worked.
BS
Femi

[femidadzie]

Thanks!

[femidadzie]

Thank you!