Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
markgraham123
Specialist
Specialist

Need to aggregate diff. of start and end time by date

Hi All,

I have ID, Start, and end time stamps.

First, I'm trying to make date out of time stamp.

second, i'm trying to aggregate the Avg, diff by one day (in our case its only one date)

Any help is highly appreciated.

Please find the attachment.

1 Solution

Accepted Solutions
sunny_talwar

For the date, you need to fix your environmental variable:

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='$#,##0.00;($#,##0.00)';

SET TimeFormat='h:mm:ss TT';

SET DateFormat='M/D/YYYY';

SET TimestampFormat='M/D/YYYY h:mm';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

Test:

LOAD * Inline [

ID, Start, End

1, 10/6/2016 16:52, 11/1/2016 8:50

2, 10/6/2016 0:00, 10/17/2016 8:23

3, 10/6/2016 13:30, 10/12/2016 8:42

4, 10/6/2016 9:13, 10/11/2016 13:59

5, 10/6/2016 13:36, 10/11/2016 15:56

6, 10/6/2016 9:37, 10/11/2016 11:31

7, 10/6/2016 12:22, 10/11/2016 12:08

8, 10/6/2016 11:53, 10/11/2016 11:33

9, 10/6/2016 11:53, 10/11/2016 11:33

];

Final:

LOAD *,

  Date(Floor(Start)) as Datestart,

  Date(Floor(End)) as DateEnd

Resident Test;

DROP Table Test;

Can you elaborate on your second requirement?

View solution in original post

1 Reply
sunny_talwar

For the date, you need to fix your environmental variable:

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='$#,##0.00;($#,##0.00)';

SET TimeFormat='h:mm:ss TT';

SET DateFormat='M/D/YYYY';

SET TimestampFormat='M/D/YYYY h:mm';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

Test:

LOAD * Inline [

ID, Start, End

1, 10/6/2016 16:52, 11/1/2016 8:50

2, 10/6/2016 0:00, 10/17/2016 8:23

3, 10/6/2016 13:30, 10/12/2016 8:42

4, 10/6/2016 9:13, 10/11/2016 13:59

5, 10/6/2016 13:36, 10/11/2016 15:56

6, 10/6/2016 9:37, 10/11/2016 11:31

7, 10/6/2016 12:22, 10/11/2016 12:08

8, 10/6/2016 11:53, 10/11/2016 11:33

9, 10/6/2016 11:53, 10/11/2016 11:33

];

Final:

LOAD *,

  Date(Floor(Start)) as Datestart,

  Date(Floor(End)) as DateEnd

Resident Test;

DROP Table Test;

Can you elaborate on your second requirement?