topic Re: calculate with condition in App Development

calculate with condition

entsh — Fri, 11 Jun 2021 11:07:08 GMT

please see the below table

form code	row	date1	date2
123	1	2001/1/1	2001/1/2
123	2	2001/1/2	2001/1/4
123	3	2001/1/4	2001/1/5
124	1	2001/1/6	2001/1/8
124	2	2001/1/8	2001/1/9
124	3	2001/1/9	2001/1/15
124	4	2011/1/15	2011/1/19
124	5	2011/1/19	2011/1/22
125	1	2011/1/15	2011/1/19
125	2	2011/1/19	2011/1/22

I want a solution through Script :

(date2 in row =3) - (date1 in row=1 )

for example for code 123 = date2 in row3(2001/1/5)-date1 in row1(2001/1/1)=4 days

result

form code	result
123	4
124	9
125	null

Re: calculate with condition

sasikanth — Fri, 11 Jun 2021 11:56:45 GMT

HI,

Try below

Load FormCode, MaxDate-MinDate as ResultDays;

Load

FormCode,

min(date1) as MinDate,

Max(date2) as MaxDate

from Table

group by FormCode;

*Preceding Load helps to calculate duration.

Re: calculate with condition

Saravanan_Desingh — Fri, 11 Jun 2021 14:56:21 GMT

Try this,

tab1: LOAD * INLINE [ form code, row, date1, date2 123, 1, 1/1/2001, 1/2/2001 123, 2, 1/2/2001, 1/4/2001 123, 3, 1/4/2001, 1/5/2001 124, 1, 1/6/2001, 1/8/2001 124, 2, 1/8/2001, 1/9/2001 124, 3, 1/9/2001, 1/15/2001 124, 4, 1/15/2011, 1/19/2011 124, 5, 1/19/2011, 1/22/2011 125, 1, 1/15/2011, 1/19/2011 125, 2, 1/19/2011, 1/22/2011 ]; Left Join(tab1) LOAD [form code], FirstSortedValue(date2,row=3) - FirstSortedValue(date1,row=1) As Result Resident tab1 Group By [form code] ;

Re: calculate with condition

Saravanan_Desingh — Fri, 11 Jun 2021 14:56:46 GMT

Output:

Re: calculate with condition

entsh — Sat, 12 Jun 2021 18:52:11 GMT

thanks a lot ,its ok but when i have only 1 form code :

tab1: LOAD * INLINE [ form code, row, date1, date2 125, 1, 1/15/2011, 1/19/2011 ]; ]; Left Join(tab1) LOAD [form code], FirstSortedValue(date2,row=3) - FirstSortedValue(date1,row=1) As Result Resident tab1 Group By [form code] ;

result is wrong

form code	result
125	4

it should be:

form code	result
125	-