Calculation of staying days.

[giozotik]

Hello,

need help

I have database of visitors that have fields

id cross_date directions

00001 1/15/2013 cross_in

00002 2/10/2013 cross_out

00003 4/14/2013 cross_in

00001 3/24/2013 cross_out

00002 5/11/2013 cross_in

00003 4/15/2013 cross_out

... ....

i need to calculate the staying 30(days) period from above mention id.

for example

id 00003 cross_in 14 Apr and cross_out 15 Apr so staying period is 1 day.

Thanks

[Nicole-Smith]

You can create a calculated field in your load script like this:

Temp:
LOAD * INLINE [
 id, cross_date, directions
 00001, 1/15/2013, cross_in
 00002, 2/10/2013, cross_out
 00003, 4/14/2013, cross_in
 00001, 3/24/2013, cross_out
 00002, 5/11/2013, cross_in
 00003, 4/15/2013, cross_out
];


Final:
LOAD id,
 cross_date AS cross_in_date
RESIDENT Temp
WHERE directions = 'cross_in';

LEFT JOIN (Final)
LOAD id,
 cross_date AS cross_out_date
RESIDENT Temp
WHERE directions = 'cross_out';

LEFT JOIN (Final)
LOAD id,
 cross_out_date - cross_in_date AS staying_period
RESIDENT Final;

DROP TABLE Temp;

This will yield a table that looks like this:

id	cross_in_date	cross_out_date	staying_period
00001	1/15/2013	3/24/2013	68
00002	5/11/2013	2/10/2013	-90
00003	4/14/2013	4/15/2013	1

[zebhashmi]

or in Designer side

=only({<directions={'cross_in'}>}cross_date)

=only({<directions={'cross_out'}>}cross_date)

=Column(1)-Column(2)

[giozotik]

Thank you Nicole