if statement

[winniethepooh]

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!

[MendyS]

HI @winniethepooh 

try this - 
count({<[Initial Status]={'Approved'}>}distinct OrderID)

regards

[BrunPierre]

Hi

Just use set analysis

Count(DISTINCT{$<[Initial Status]={'Approved'}>} OrderID)

[winniethepooh]

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:

[sidhiq91]

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

[winniethepooh]

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 😔

[winniethepooh]

maybe this explains better

[Saravanan_Desingh · Accepted solution]

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;

[winniethepooh]

It worked! 😍

Thank you for helping!