Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I have default data that looks like below, where for each order id i can have multiple values in field (Initial Status):
OrderID | Initial Status | New Status | Orders |
Order 1 | Approved | Approved | 1 |
Canceled | Approved | 0 | |
Declined | Approved | 0 | |
WIP | Approved | 0 | |
Order 2 | WIP | WIP | 1 |
Canceled | WIP | 0 | |
Declined | WIP | 0 | |
Order 3 | Declined | Declined | 1 |
Canceled | Declined | 0 | |
Order 4 | Canceled | Canceled | 1 |
I want to count unique orders depending on values inside status field : if status = approved, count it only once, no matter what other status this order has or return a new status as 'approved'; The 4 orders from above are 4 situations possible.
Thank you in advance for your help!
One solution through Script:
tab1:
LOAD RowNo() As ID,*;
LOAD * INLINE [
OrderID, Initial Status
Order 1, Approved
Order 1, Canceled
Order 1, Declined
Order 1, WIP
Order 2, WIP
Order 2, Canceled
Order 2, Declined
Order 3, Declined
Order 3, Canceled
Order 4, Canceled
];
Left Join(tab1)
LOAD OrderID, If(Index(Concat(DISTINCT [Initial Status]),'Approved')>0, 'Approved', FirstSortedValue([Initial Status],ID)) As New_Status
Resident tab1
Group By OrderID;
Left Join(tab1)
LOAD OrderID, [Initial Status],New_Status, If([Initial Status]=New_Status,1,0) As Orders
Resident tab1;
Hi
Just use set analysis
Count(DISTINCT{$<[Initial Status]={'Approved'}>} OrderID)
Tried that already. But because my DB means default multiple status for the same order, i need to 'choose' one (prioritize status).
Expression from above gives me this result. And i want this to bring me '1' only on Approved, and 0 to any others.
Another case is when i don't have 'approved status' in my DB,just like below=> in this case i want 0 for Approve, 1 for WIP, and 0 for all other:
@winniethepooh there are couple of ways to get this done.
Either use set analysis like below:
Count(distinct {<[Initial Status]={'Approved'}>}OrderID)
Or use the normal if statement in the expression:
Count(if([Initial Status]='Approved', OrderID))
Unfortunately, this doesn't work as i have my order with 4 different statuses at the same time and i need to bring in the report only one, depending on their logic for business 😔
maybe this explains better
One solution through Script:
tab1:
LOAD RowNo() As ID,*;
LOAD * INLINE [
OrderID, Initial Status
Order 1, Approved
Order 1, Canceled
Order 1, Declined
Order 1, WIP
Order 2, WIP
Order 2, Canceled
Order 2, Declined
Order 3, Declined
Order 3, Canceled
Order 4, Canceled
];
Left Join(tab1)
LOAD OrderID, If(Index(Concat(DISTINCT [Initial Status]),'Approved')>0, 'Approved', FirstSortedValue([Initial Status],ID)) As New_Status
Resident tab1
Group By OrderID;
Left Join(tab1)
LOAD OrderID, [Initial Status],New_Status, If([Initial Status]=New_Status,1,0) As Orders
Resident tab1;
It worked! 😍
Thank you for helping!