Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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') )
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],
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....
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.
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') ))
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.
Unfortunately that did not work.
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.
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...
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],
Updated app