Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
I am trying to create a range between my min and max timestamps.
this script works when using dates, but for some reason when I use timestamp it gives me the bellow error
is it my variables that need a format change because of the timestamp? or what could it be???
I appreciate your assistance
Error in expression:
')' expected
TimeStamp:
LOAD date(2017/06/01 00:00:00+recno()-1) as "TimeRange"
AUTOGENERATE 2017/06/12 06:12:39-2017/06/01 00:00:00+1
Tasks:
Load * ,
Timestamp(([Timestamp to] -[Timestamp From]),'hh:mm:ss') as TaskDuration
Resident TasksNew;
Drop Table TasksNew;
Min:
LOAD min(Timestamp(("Timestamp From"),'YYYY/MM/DD hh:mm:ss')) as "Min Date"
;
LOAD fieldvalue('Timestamp From',iterno()) as "Timestamp From"
AUTOGENERATE 1
WHILE len(fieldvalue('Timestamp From',iterno()))
;
Max:
LOAD max(Timestamp(("Timestamp to"),'YYYY/MM/DD hh:mm:ss')) as "Max Date"
;
LOAD fieldvalue('Timestamp to',iterno()) as "Timestamp to"
AUTOGENERATE 1
WHILE len(fieldvalue('Timestamp to',iterno()))
;
LET vMin = fieldvalue('Min Date',1);
LET vMax = fieldvalue('Max Date',1);
TimeStamp:
LOAD date($(vMin)+recno()-1) as "TimeRange"
AUTOGENERATE $(vMax)-$(vMin)+1
;
LEFT JOIN (Tasks)
INTERVALMATCH (TimeRange)
LOAD
"Timestamp From"
,"Timestamp to"
RESIDENT [Tasks]
;
DROP TABLES
Min
,Max
,TimeStamp
;
Use these guys within Single quotes here:
TimeStamp:
LOAD date('$(vMin)'+recno()-1) as "TimeRange"
AUTOGENERATE '$(vMax)'-'$(vMin)'+1;
Hi Sunny
I tried that but then get this error...
Script line error:
TimeStamp:
LOAD date('2017/06/01 00:00:00'+recno()-1) as "TimeRange"
AUTOGENERATE '2017/06/12 06:12:39'-'2017/06/01 00:00:00'+1
May be change these guys to Peek() instead of Field Value
LET vMin = Peek('Min Date');
LET vMax = Peek('Max Date');
I tried that as suggested now Sunny, and it returned the same error...
Would you be able to share a value that you use for Timestamp From and Timestamp To. Make sure you give me the same format you have them in.
Timestamp from : 17/06/11 03:06:52
Timestamp to : 17/06/11 00:37:52
Check this out
Tasks:
LOAD Num(From) as From,
Num(To) as To;
LOAD * INLINE [
From, To
17/06/11 03:06:52, 17/06/23 00:37:52
];
Min:
LOAD Min(MinFrom) as MinFrom;
LOAD Num(FieldValue('From', IterNo())) as MinFrom
AutoGenerate 1
While FieldValueCount('From') >= IterNo();
Max:
LOAD Min(MaxTo) as MaxTo;
LOAD Num(FieldValue('To', IterNo())) as MaxTo
AutoGenerate 1
While FieldValueCount('To') >= IterNo();
LET vMin = Peek('MinFrom', 0, 'Min');
LET vMax = Peek('MaxTo', 0, 'Max');
TimeStamp:
LOAD Date(Floor('$(vMin)') + RecNo() - 1) as "TimeRange"
AutoGenerate Floor('$(vMax)') - Floor('$(vMin)') + 1;
LEFT JOIN (Tasks)
INTERVALMATCH (TimeRange)
LOAD From,
To
RESIDENT [Tasks];
Left Join (Tasks)
LOAD *
Resident TimeStamp;
DROP Tables Min, Max, TimeStamp;
When I replicate your script my TimeRange field returns a blank...
I notice your Timerange does not actually have a time in it? I need the hours minutes and seconds to be returned...
Is there perhaps a standard qlik model that shows the number of tasks running on qmc for each minute of the day ?
That is really what I am trying to get to...