topic Re: Interval Match in QlikView

Interval Match

Fri, 02 Oct 2015 09:15:57 GMT

Hi,

I want to build a table which creates Intervals für several ID´s. In my example you will see an data set with ID = 1; Start = 09:00:00 and End = 11:30:00!

Now my goal is, to build a table which gives back an Interval Match for the ID 1 in 30 Min Steps. The final table should look like this...

ID / Time

1 / 09:00:00

1 / 09:30:00

1 / 10:00:00

1 / 10:30:00

1 / 11:00:00

1 / 11:30:00

Can anyone help me to build this or got everyone an example...

Thank you

Re: Interval Match

MK_QSL — Fri, 02 Oct 2015 09:50:11 GMT

Table:

LOAD * INLINE [

ID, Start, End

1, 09:00:00, 11:30:00

];

NoConcatenate

Final:

Load

ID,

Time(Start + Time#('30','mm')*(IterNo()-1),'hh:mm:ss') as StartNew,

Time(Start + Time#('29:59','mm:ss')*(IterNo()),'hh:mm:ss') as EndNew

Resident Table

While Time(Start + Time#('30','mm')*(IterNo()-1),'hh:mm:ss') <= End;

Drop Table Table;

Drop Field EndNew;

===================================

If you want EndNew, don't drop it..

Re: Interval Match

Mon, 05 Oct 2015 09:20:52 GMT

I think that is not a problem for the created tabel but I have seen that the EndNew column is going to increase the seconds with every pass. How can I fix that?

Re: Interval Match

MK_QSL — Mon, 05 Oct 2015 09:37:55 GMT

I am not getting what you are trying to say.. Can you please elaborate little more with possible example..?

Re: Interval Match

Mon, 05 Oct 2015 12:06:56 GMT

Okay, I have another problem. I want to make an comparison between Minutes and Seconds. Normally based on the Table the result from the expression must be ID 1, Start 09:00:00 is -> 1, and ID 2, Start 09:34:00 is -> 0. But at the moment I become both 1. What I am doing wrong?

Table:

LOAD * INLINE [

ID, Start

1, 09:00:00

2, 09:34:00

];

if( Time(time#(Start, 'mm:ss') > time#('00:00', 'mm:ss') AND time#(Start, 'mm:ss') < time#('30:00', 'mm:ss'), 'mm:ss'), 1, 0) as Tester

Re: Interval Match

MK_QSL — Mon, 05 Oct 2015 12:17:19 GMT

Table:

LOAD * INLINE [

ID, Start, End

1, 09:00:00, 11:30:00

];

NoConcatenate

Final:

Load

ID,

Time(Start + Time#('30','mm')*(IterNo()-1),'hh:mm:ss') as StartNew,

If(Time(Start + Time#('30','mm')*(IterNo()-1),'hh:mm:ss') > Time#('09:34:00','hh:mm:ss'),2,1) as Tester,

Time(Start + Time#('29:59','mm:ss')*(IterNo()),'hh:mm:ss') as EndNew

Resident Table

While Time(Start + Time#('30','mm')*(IterNo()-1),'hh:mm:ss') <= End;

Drop Table Table;

Drop Field EndNew;