Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On Demand Webinar: See Why Thousands of QlikView Users Have Switched to Qlik Sense. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Need Help with Date

Hi

I have a table like this. Here i have created a Master Calendar using the start_time column. Now in this table i have an start_Time and End_Time Column and duration_seconds table is interval between end_time and start_time in seconds. but my requirement is to find out and display the data with day wise with its duration  . There is no issue if the start_time and end_time is same day but when the start_time and end_time different for example KEY value 210924 started on 1st May and ended on 4th May then how can we get the duration of each date starting from 1st May to 4th May individually . Please help

Also if the End_Time is NULL , then it means the Process is still running so i need to get the data of each date starting from starting time till current day until the duration time.

   

IDKEYstart_timeend_timeduration_seconds
2538561924574/7/2015 16:084/7/2015 18:127469
2538751929444/7/2015 16:204/7/2015 18:126760
2780912087954/20/2015 23:484/21/2015 0:121414
2867192109244/28/2015 0:264/28/2015 0:28113
2895632109245/1/2015 20:585/4/2015 17:08245425
2895642116465/1/2015 20:595/4/2015 17:08245369
2965192173825/11/2015 17:32NULL607225
3080912087955/25/2015 11:485/25/2015 12:121394
1 Solution

Accepted Solutions
Gysbert_Wassenaar

See attached example.


talk is cheap, supply exceeds demand

View solution in original post

3 Replies
Gysbert_Wassenaar

See attached example.


talk is cheap, supply exceeds demand

View solution in original post

rwunderlich
MVP & Luminary
MVP & Luminary

It depends on how you define a day duration. If it's a 24 hour period, then

=floor(end - start)      // # of 24 periods

if it's calendar days, the:

=floor(end) - floor(start)      // # of calendar days


for the NULL end in either case, use an if() to provide the missing date::

=floor(if(IsNull(end), today(2), end))  - floor(start)


-Rob

http://masterssummit.com

http://robwunderlich.com


Not applicable

gwassenaar

The solution you have given is working fine with the End_Time column as NULL but now i am getting the same field as \N  value instead of NULL when i am fetching from DB

The below line may be   creating problem and no record is getting added to the table

timestamp(alt(timestamp#(end_time, 'MM/DD/YYYY h:mm'),now()),'M/D/YYYY h:mm') as end_time,

and

LOAD *, rangemax(daystart(start_time) + IterNo() -1,start_time) as s_time

while iterno() <= ceil(end_time - floor(start_time));

Could you please suggest how to handle \N in qlikview and above line