Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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 |
or in Designer side
=only({<directions={'cross_in'}>}cross_date)
=only({<directions={'cross_out'}>}cross_date)
=Column(1)-Column(2)
Thank you Nicole