View data in next row

[ciaran_mcgowan]

Hi all,

Is it possible to view the value of a field in the next row? I'm looking to flag a record if the record that follows it meets certain criteria, then flag that following record if it's previous one meets certain criteria. Basically, I need to display records that occur within 2 hours of each other. So far I have this:

if(Total_Hours-Previous(Total_Hours)<=2, 1) AS Flag

This works fine for returning the second or third records that occurs in less than 2 hours but it neglects the first one most of the time. This is what I'm getting with the Previous() function:

ID	JOB_COMPLETE	TIME	TOTAL_HOURS	FLAG
1	Yes	09:00	0
2	Yes	12:00	3
3	Yes	13:00	4	1
4	Yes	14:30	5:30	1
5	Yes	16:45	7:45
6	Yes	17:30	8:30	1
7	Yes	18:00	9:00	1

You can see that it's missed record #2 as record #1 was three hours before it, even though I don't even want to include it. I need to show both records that are with the time period so if there is a way to look ahead in the load script I can use that in conjunction with Previous() (like below):

ID	JOB_COMPLETE	TIME	TOTAL_HOURS	FLAG
2	Yes	12:00	3	1
3	Yes	13:00	4	1
4	Yes	14:30	5:30	1
6	Yes	17:30	8:30	1
7	Yes	18:00	9:00	1

As always, if anyone has any better approaches/recommendations, I would be extremely grateful.

Thanks.

[jagan]

Hi,

Try this script

Data:

LOAD

*,

if(DecimalHours-Alt(Previous(DecimalHours), 0)<= 2, 1) AS Flag;

LOAD

*,

Num((Num(SubField(TOTAL_HOURS, ':', 1)) + Alt(Num(SubField(TOTAL_HOURS, ':', 2)/60, '##0.##'), 0)), '##0.##') AS DecimalHours;

LOAD ID,

     JOB_COMPLETE,

     TIME,

     TOTAL_HOURS

FROM

[http://community.qlik.com/thread/110637]

(html, codepage is 1252, embedded labels, table is @1);

Regards,

Jagan.

[ciaran_mcgowan]

Hi Jagan,

I'm not sure what you're suggesting. Are these three separate loads?

[jagan]

Hi,

Try this script and check, all are single load only, it is call Precedent Load.

Regards,

Jagan.

[maxgro · Accepted solution]

I read your data twice

- first time to set the flag based on time diff (2 hour)

- second time in reverse order to add the missing record; see newflag

RESULT IS

SCRIPT IS

DIRECTORY;

Source:

LOAD ID,

    JOB_COMPLETE,

    TIME,

    TOTAL_HOURS

FROM

[http://community.qlik.com/thread/110637]

(html, codepage is 1252, embedded labels, table is @1);

Table:

LOAD

  ID,

  JOB_COMPLETE,

  TIME,

  interval(TIME-Peek(TIME), 'hh:mm') as TOTAL_HOURS,

  if(interval(TIME-Peek(TIME), 'mm') <= interval('09:00'-'07:00', 'hh:mm') ,1)  as flag

Resident

  Source;

DROP Table Source;

Final:

LOAD

  ID,

  JOB_COMPLETE,

  TIME,

  TOTAL_HOURS,

  flag,

  if(peek(flag)=1 or flag=1,1) as newflag

Resident

  Table

order by ID desc;

DROP Table Table;

[ciaran_mcgowan]

Hi Jagan,

Thanks for your help but I couldn't get that to work. I ended up going with Massimo's solution in the end.

[ciaran_mcgowan]

Hi Massimo,

Thanks for your help. This is the first solution I thought of but didn't want to go with it as it seem inefficient but after spending some time trying to find a better way, I think in this case the simplest solution is the best one.

Thanks you.

Ciarán