topic Re: Interval Match in QlikView

Interval Match

bimanbeginner — Wed, 25 Nov 2020 16:16:04 GMT

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
;

Re: Interval Match

sunny_talwar — Wed, 14 Jun 2017 07:28:55 GMT

Use these guys within Single quotes here:

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

Re: Interval Match

bimanbeginner — Wed, 14 Jun 2017 07:36:39 GMT

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

Re: Interval Match

sunny_talwar — Wed, 14 Jun 2017 07:38:18 GMT

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

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

Re: Interval Match

bimanbeginner — Wed, 14 Jun 2017 07:42:11 GMT

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

Re: Interval Match

sunny_talwar — Wed, 14 Jun 2017 07:45:53 GMT

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.

Re: Interval Match

bimanbeginner — Wed, 14 Jun 2017 07:49:13 GMT

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

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

Re: Interval Match

sunny_talwar — Wed, 14 Jun 2017 08:07:58 GMT

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,

RESIDENT [Tasks];

Left Join (Tasks)

LOAD *

Resident TimeStamp;

DROP Tables Min, Max, TimeStamp;

Re: Interval Match

bimanbeginner — Wed, 14 Jun 2017 09:54:04 GMT

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

Re: Interval Match

bimanbeginner — Wed, 14 Jun 2017 09:56:37 GMT

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

Re: Interval Match

sunny_talwar — Wed, 14 Jun 2017 11:06:07 GMT

I showed you the while approach in another post, is that not working for you?