topic Re: if statement in App Development

if statement

winniethepooh — Sun, 05 Feb 2023 09:53:05 GMT

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
Order 3	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!

Re: if statement

MendyS — Sun, 05 Feb 2023 11:54:05 GMT

HI @winniethepooh

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

regards

Re: if statement

BrunPierre — Sun, 05 Feb 2023 12:07:04 GMT

Just use set analysis

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

Re: if statement

winniethepooh — Sun, 05 Feb 2023 12:29:32 GMT

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:

Re: if statement

sidhiq91 — Sun, 05 Feb 2023 12:35:35 GMT

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

Re: if statement

winniethepooh — Sun, 05 Feb 2023 12:46:59 GMT

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 😔

Re: if statement

winniethepooh — Sun, 05 Feb 2023 12:55:01 GMT

maybe this explains better

Re: if statement

Saravanan_Desingh — Sun, 05 Feb 2023 18:10:51 GMT

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;

Re: if statement

winniethepooh — Sun, 05 Feb 2023 20:04:55 GMT

It worked! 😍

Thank you for helping!