topic Re: How to get maximum and minimum timestamp? in QlikView

How to get maximum and minimum timestamp?

Anonymous — Wed, 04 Apr 2018 09:17:57 GMT

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:

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.

Re: How to get maximum and minimum timestamp?

MK_QSL — Wed, 04 Apr 2018 09:20:55 GMT

Search for FirstSortedValue function

Re: How to get maximum and minimum timestamp?

gerry_hdm — Wed, 04 Apr 2018 09:33:42 GMT

Firstsortedvalue

lastsortedvalue

by Group statement

Re: How to get maximum and minimum timestamp?

Anonymous — Wed, 04 Apr 2018 09:36:49 GMT

Sir, do I write this in the Load Script or in the Expression tab?

Re: How to get maximum and minimum timestamp?

gerry_hdm — Wed, 04 Apr 2018 09:38:12 GMT

by load script

Re: How to get maximum and minimum timestamp?

gerry_hdm — Wed, 04 Apr 2018 09:41:46 GMT

MYTimestamp:

load OutageID,

firstsortedvalue(Timestamp) as FirstTimestamp,

firstsortedvalue(Timestamp) as FirstTimestamp

resident yourtable

group by OutageID

;

Re: How to get maximum and minimum timestamp?

gerry_hdm — Wed, 04 Apr 2018 09:42:42 GMT

Greetings Gerold

Re: How to get maximum and minimum timestamp?

olivierrobin — Wed, 04 Apr 2018 09:46:16 GMT

hello

for me , lastsortedvalue doesn't exist (that would be a good idea)

so i think you mean

MYTimestamp:

load OutageID,

firstsortedvalue(Timestamp,Timestamp) as FirstTimestamp,

firstsortedvalue(Timestamp,-Timestemp) as LastTimestamp

resident yourtable

group by OutageID

;

Re: How to get maximum and minimum timestamp?

gerry_hdm — Wed, 04 Apr 2018 09:48:26 GMT

you can create a test.qvw ?

Re: How to get maximum and minimum timestamp?

sunny_talwar — Wed, 04 Apr 2018 11:25:38 GMT

Would you be able to provide the Excel file above and also provide the expected output?

Re: How to get maximum and minimum timestamp?

Anonymous — Thu, 05 Apr 2018 01:46:03 GMT

Sir, this is how my load script looks like:

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

Re: How to get maximum and minimum timestamp?

Anonymous — Thu, 05 Apr 2018 01:56:59 GMT

Hi Sir please see attached file.

Thank you.

Re: How to get maximum and minimum timestamp?

Anonymous — Thu, 05 Apr 2018 02:20:54 GMT

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

Re: How to get maximum and minimum timestamp?

gerry_hdm — Thu, 05 Apr 2018 06:22:06 GMT

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];

Re: How to get maximum and minimum timestamp?

Anonymous — Thu, 05 Apr 2018 06:30:01 GMT

Hi Sir, unfortunately it isn't working

Re: How to get maximum and minimum timestamp?

gerry_hdm — Thu, 05 Apr 2018 06:41:08 GMT

here is a example

Re: How to get maximum and minimum timestamp?

Anonymous — Thu, 05 Apr 2018 06:42:06 GMT

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