topic Re: View data in next row in QlikView

View data in next row

ciaran_mcgowan — Fri, 14 Mar 2014 00:32:15 GMT

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.

Re: View data in next row

jagan — Fri, 14 Mar 2014 02:31:21 GMT

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.

Re: View data in next row

ciaran_mcgowan — Fri, 14 Mar 2014 20:02:34 GMT

Hi Jagan,

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

Re: View data in next row

jagan — Sat, 15 Mar 2014 06:51:11 GMT

Hi,

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

Regards,

Jagan.

Re: View data in next row

maxgro — Sat, 15 Mar 2014 10:11:26 GMT

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;

Re: View data in next row

ciaran_mcgowan — Tue, 18 Mar 2014 12:12:39 GMT

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.

Re: View data in next row

ciaran_mcgowan — Tue, 18 Mar 2014 12:14:32 GMT

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