calculate with condition

[entsh]

hi

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

[sasikanth]

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. 

[Saravanan_Desingh]

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]
;

[Saravanan_Desingh · Accepted solution]

Output:

[entsh]

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	-