Interval Match

[bimanbeginner]

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
;

[sunny_talwar]

Use these guys within Single quotes here:

TimeStamp:
LOAD date('$(vMin)'+recno()-1) as "TimeRange"
AUTOGENERATE '$(vMax)'-'$(vMin)'+1;

[bimanbeginner]

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

[sunny_talwar]

May be change these guys to Peek() instead of Field Value

LET vMin = Peek('Min Date');
LET vMax = Peek('Max Date');

[bimanbeginner]

I tried that as suggested now Sunny, and it returned the same error...

[sunny_talwar]

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.

[bimanbeginner]

Timestamp from : 17/06/11 03:06:52

Timestamp to     : 17/06/11 00:37:52

[sunny_talwar]

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;

[bimanbeginner]

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...

[bimanbeginner]

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...