Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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;
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.
Hi Jagan,
I'm not sure what you're suggesting. Are these three separate loads?
Hi,
Try this script and check, all are single load only, it is call Precedent Load.
Regards,
Jagan.
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;
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.
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