Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

giozotik
New Contributor II

Calculation of staying days.

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

Tags (2)
3 Replies

Re: Calculation of staying days.

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
000011/15/20133/24/201368
000025/11/20132/10/2013-90
000034/14/20134/15/20131


zebhashmi
Valued Contributor

Re: Calculation of staying days.

or in Designer side

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

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

=Column(1)-Column(2)

Capture4.PNG

giozotik
New Contributor II

Re: Calculation of staying days.

Thank you Nicole

Community Browser