How to code a LOOP?

Tue, 08 Jan 2013 17:12:46 GMT

I’m looking for help with coding a LOOP which I’ve never done before. Do I do this in the SELECT statement or the LOAD statement? Basically, what I want to do is create a StartDttm for each log entry associated with a Job Report. As is the timelog table only stores the number of hours that a particular entry took. Their dates would be derived from the Job Report StartDttm with the first entry's StartDttm equaling the Job's StartDttm. The remaining Log Entries StartDttms would be derived by adding the HrsDur of the previous ones to the Job Report StartDttm or possibly from the previous derived StartDttm.

Logic:

SELECT

RptID,

RptStartDttm,

RptEndDttm,

LogID,

TimeCode,

LogSeq#,

HrsDur,

CASE WHEN LogSeq# = 1 THEN RptStartDttm // -- from here down is just my thoughts on the logic

ELSE

DO WHILE …… ??? ……

LogSeq# = 2 THEN RptStartDttm + LogSeq#1 HrsDur

LogSeq# = 3 THEN RptStartDttm + LogSeq#1 HrsDur + LogSeq#2 HrsDur

LogSeq# = 4 THEN RptStartDttm + LogSeq#1 HrsDur + LogSeq#2 HrsDur + LogSeq#3 HrsDur

etc. until last LogSeq# for RptID

--- (OR) ---

LogSeq# = 2 THEN RptStartDttm + LogSeq#1 HrsDur

LogSeq# = 3 THEN LogSeq#2 OpsStartDttm + LogSeq#2 HrsDur

LogSeq# = 4 THEN LogSeq#3 OpsStartDttm + LogSeq#3 HrsDur

etc. until last LogSeq# for RptID

END AS OpsStartDttm

FROM

JobRpt,

TimeLog

WHERE

JobRpt.RptID = TimeLog.RptID

SQL Tables:

JobRpt

RptID RptStartDttm RptEndDttm

R1 01/13/2013 06:00 01/14/2013 06:00

R2 01/14/2013 06:00 01/15/2013 06:00

TimeLog

LogID RptID HrsDur TimeCode LogSeq#

L1A R1 9 MIRU 1

L2B R1 1 BHA 2

L3C R1 4 DR 3

L4D R1 2 SVY 4

L1AF R2 5 BHA 1

L21G R2 6 DR 2

L31D R2 7 RR 3

QlikView Table:

Operations

RptID RptStartDttm RptEndDttm LogID TimeCode LogSeq# HrsDur OpsStartDttm (derived field)

R1 01/13/2013 06:00 01/14/2013 06:00 L1A MIRU 1 9 01/13/2013 06:00

R1 01/13/2013 06:00 01/14/2013 06:00 L2B BHA 2 1 01/13/2013 15:00

R1 01/13/2013 06:00 01/14/2013 06:00 L3C DR 3 4 01/13/2013 16:00

R1 01/13/2013 06:00 01/14/2013 06:00 L4D SVY 4 2 01/13/2013 20:00

R2 01/14/2013 06:00 01/15/2013 06:00 L1AF BHA 1 5 01/14/2013 06:00

R2 01/14/2013 06:00 01/15/2013 06:00 L21G DR 2 6 01/14/2013 11:00

R2 01/14/2013 06:00 01/15/2013 06:00 L31D RR 3 7 01/14/2013 17:00

Re: How to code a LOOP?

Gysbert_Wassenaar — Tue, 08 Jan 2013 17:54:36 GMT

Something like this:

JobRpt:

load RptID,

timestamp#(RptStartDttm,'MM/DD/YYYY hh:mm') as RptStartDttm,

timestamp#(RptEndDttm,'MM/DD/YYYY hh:mm') as RptEndDttm

inline [

RptID,RptStartDttm,RptEndDttm

R1,01/13/2013 06:00,01/14/2013 06:00

R2,01/14/2013 06:00,01/15/2013 06:00

];

join load * inline [

LogID,RptID,HrsDur,TimeCode,LogSeq#

L1A,R1,9,MIRU,1

L2B,R1,1,BHA,2

L3C,R1,4,DR,3

L4D,R1,2,SVY,4

L1AF,R2,5,BHA,1

L21G,R2,6,DR,2

L31D,R2,7,RR,3

];

Timelog:

load LogID,RptID,HrsDur,TimeCode,LogSeq#,RptStartDttm,RptEndDttm,

timestamp(if(RptID=Previous(RptID), peek('OpsStartDttm') + peek('HrsDur')/24, RptStartDttm),'MM/DD/YYYY hh:mm') as OpsStartDttm

Resident JobRpt order by RptID,LogSeq#;

drop table JobRpt;

Re: How to code a LOOP?

Wed, 09 Jan 2013 15:24:12 GMT

Thanks a lot for your help. I don't really understand how the code works so I'll have to study it more when I've got the time. I like that it's just one line that does the trick. However, I did have to change Timestamp#() to Date() in my code for it to work and display properly.

topic Re: How to code a LOOP? in QlikView

How to code a LOOP?

Re: How to code a LOOP?

Re: How to code a LOOP?