Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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];
////////////////////////////////////////////////////////////////////
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];
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];
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 |
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];