Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

camjvine18
Contributor

Timestamp affecting the daily view

i have converted a string to a timestamp  in the load script labelled Load Data, ie.

Timestamp(Timestamp#([start],'MMM DD YYYY hh:mmTT')) AS [Time Start],

but now it does not return values in the sheet Daily Summary in several columns for vYesterday of which it did before. This is in the load script labelled Main.

Eg. Source Start column

=Min({1<Date = {'$(=Date(vYesterday))'},System={'Source'}>}

Time(Time#(Trim(Right([Time Start],8)),'hh:mmTT'),'hh:mm') )

1 Solution

Accepted Solutions
camjvine18
Contributor

Re: Timestamp affecting the daily view

Fixed it. Issue was with spaces thus was instructed to use [ ].

If( IterNo() = 1 , Timestamp#([Source Start],'MMM [ ]D [ ]YYYY [ ]hh:mmTT'), Timestamp#([MaRRs Start],'MMM [ ]D [ ]YYYY [ ]hh:mmTT')) AS [Time Start],

10 Replies
MVP
MVP

Re: Timestamp affecting the daily view

Did you attach the wrong QVF? It is no variable named vYestday in your application. Furthermore there are no columns named "Source Start" with the expression that you mention....

camjvine18
Contributor

Re: Timestamp affecting the daily view

Apologies you are correct. I cannot access the server version of this today. Just cant seem to make sense of why before I put Timestamp syntax around start that it would give no values. I can still get =Date('$(=Date(vYesterday))','DD/MM/YY') to produce the date of 4/1 but no output in Source Start.

pradosh_thakur
Valued Contributor III

Re: Timestamp affecting the daily view

try this

file is 36 mb so didnt open it. i tried to chnge min to time(min()) and trim to purgechar() so you won't get into thr trouble of space in the data. plese check if right function is needed and give it a try

=time(Min({1<Date = {'$(=Date(vYesterday))'},System={'Source'}>}

Time(Time#(purgechar(Right([Time Start],8),' '),'hh:mmTT'),'hh:mm') ))

MVP
MVP

Re: Timestamp affecting the daily view

A safer approach when using dates in Set Expressions is to avoid using formatted dates. Formated dates are more for display purposes. Real date fields in Qlik will have a numeric serial date value also due to the dual nature of Qlik fields.

The expressions will be more reliable and simpler and most likely be quicker in calculations too.

camjvine18
Contributor

Re: Timestamp affecting the daily view

Unfortunately that did not work.

camjvine18
Contributor

Re: Timestamp affecting the daily view

Here is the app and load file. Still cant get the Source Start figures to appear.

What do you mean by your last statement? I initially had the Time Start dates as text but subsequently need to turn them into real dates for further analysis. The expression here Time(Time#(Trim(Right(sla,8)),'hh:mmTT'),'hh:mm') should now not be used because of the new load script making it a real date.

MVP
MVP

Re: Timestamp affecting the daily view

1) You have a "typo" in the load script when formatting the vYesterday date:

LET vYesterday = Date(Today() - 2, 'YYYMMDD');

There are just three digits in the year. So it calculates to be 0180104 but should be 20180104. In this case the underlying numeric date is still 4th of January 2018 but it will be displayed as 0180104 if you don't override the display format in an expression. If you're lucky it will not matter. However the error is best to correct.

2) Your data has just null values for [Start Date] so it is not strange that the expression for the column named "Source Start" does not calculate to anything else than NULL or a dash visually...

camjvine18
Contributor

Re: Timestamp affecting the daily view

1) Typo my error as recreated the app.

2) The problem is that when i use this load i get a value, but when I attempt to run it into a real date I dont get a value

If( IterNo() = 1 , [Source Start],[MaRRS Start]) AS [Time Start],

If( IterNo() = 1 , [Source End],[MaRRS End]) AS [Time End],  

camjvine18
Contributor

Re: Timestamp affecting the daily view

Updated app