Skip to main content
Announcements
See why Qlik is a Leader in the 2024 Gartner® Magic Quadrant™ for Analytics & BI Platforms. Download Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

How to get maximum and minimum timestamp?

Hey guys! I have a Start Date and End Date which has Start time and End time respectively. My task is to get the minimum and maximum timestamp for each outage, excluding Duration Type and Major Event Day (MED).

Here's how my data set looks like:

1.PNG

I tried min(timestamp#([Start Date],[Start Time],[Outage ID])) and max(timestamp#([Start Date],[Start Time],[Outage ID])) to no avail. I am a QlikView newbie so I'm sorry if I missed something obvious and thanks for your response in advance.

16 Replies
Anonymous
Not applicable
Author

Sir, this is how my load script looks like:

2.PNG

Do I write the MYTimestamp script after From? or below Load Outage ID?

Anonymous
Not applicable
Author

Hi Sir please see attached file.

Thank you.

Anonymous
Not applicable
Author

Also, the data should be presented in a Straight Table.

gerry_hdm
Creator II
Creator II

you are Setting a correctly timestamp

Directory;
DATEMAXMININPUT:
LOAD [Outage ID] ,
[Start Date]  ,
[Start Time]  ,
timestamp(Timestamp# (text(date([Start Date],'DD.MM.YYYY')) & text(time([Start Time],'hh:mm:ss')) ,'DD.MM.YYYYhh:mm:ss' ),'DD.MM.YYYY hh:mm:ss')  as START_Timestamp  ,
[End Date]  ,
[End Time]  ,
timestamp(Timestamp# (text(date([End Date],'DD.MM.YYYY')) & text(time([End Time],'hh:mm:ss')) ,'DD.MM.YYYYhh:mm:ss' ),'DD.MM.YYYY hh:mm:ss')  as END_Timestamp  ,
[Duration Type],
[Major Event Day],
[General Cause]
FROM
[Excel File.xlsx]
(
ooxml, embedded labels, table is Data);

TimeStamp:
load *,
(
maxTIMESTAMP - minTIMESTAMP) *60 *24 as Duration
;

Load
[Outage ID],
min(START_Timestamp) as minTIMESTAMP,
Max(END_Timestamp) as maxTIMESTAMP
resident DATEMAXMININPUT 
group by [Outage ID];

Anonymous
Not applicable
Author

Hi Sir, unfortunately it isn't working

3.PNG

gerry_hdm
Creator II
Creator II

here is a example

Anonymous
Not applicable
Author

Sorry to say Sir but I'm using Personal version so I can't open the file on QlikView