Correlated subquery in qlikview

[#1]

Hi All,

I'm trying to achieve similar results in Qlikview script as you'd get from a correlated subquery in any database. I have a dataset as below;

ComponentName            Action            ActionDate

Comp-A                         START           26-Feb-2013 10:30:00

Comp-B                         START           26-Feb-2013 10:50:00

Comp-A                         STOP            26-Feb-2013 12:40:00

Comp-A                         START           26-Feb-2013 14:30:00

Comp-A                         STOP            26-Feb-2013 19:30:00

Comp-B                         STOP            26-Feb-2013 19:30:00

Comp-C                         START           26-Feb-2013 21:50:00

Comp-C                         STOP            26-Feb-2013 22:30:00

From the above data, I wish to analyse what component was running at any given time. For example,

at  12:00  - A, B

at  14:00  - B

at   16:00 - A, B

at  20:00  -  None

at  22:00  - C

This is easily done in database using subquery but can this be done using qlikview script as well? I need to do this for every hour of the day for a much larger set of data.

Any guidance to achive such a result is much appreciated.

Regards

Vikas

[Anonymous · Accepted solution]

Hi Vikas,

It sounds like you could use IntervalMatch to accomplish this.

[#3]

Thanks Johannes

IntervalMatch did solve my problem. I just had to create intervals in one record i.e. merge sets of records into one to get start/end time in one row which I did in database itself.

Appreciate your help.

Vikas