How to create next row in script - Qlik Community

GauravKumar1 · ‎2019-02-08

Dear Team,

i stuck a requirement that how to create a next row in new field.

requirement is below table

Date	Name	Shift in	Shift Out
01-04-18	A	7:00:00	13:00:00
02-04-18	A	8:00:00	14:00:00
03-04-18	A	9:00:00	15:00:00
04-04-18	A	10:00:00	16:00:00
05-04-18	A	11:00:00	17:00:00
01-04-18	B	7:00:00	13:00:00
02-04-18	B	8:00:00	14:00:00
03-04-18	B	9:00:00	15:00:00
04-04-18	B	10:00:00	16:00:00
05-04-18	B	11:00:00	17:00:00
01-04-18	C	7:00:00	13:00:00
02-04-18	C	8:00:00	14:00:00
03-04-18	C	9:00:00	15:00:00
04-04-18	C	10:00:00	16:00:00
05-04-18	C	11:00:00	17:00:00
01-04-18	D	7:00:00	13:00:00
02-04-18	D	8:00:00	14:00:00
03-04-18	D	9:00:00	15:00:00
04-04-18	D	10:00:00	16:00:00
05-04-18	D	11:00:00	17:00:00
01-04-18	E	7:00:00	13:00:00
02-04-18	E	8:00:00	14:00:00
03-04-18	E	9:00:00	15:00:00
04-04-18	E	10:00:00	16:00:00
05-04-18	E	11:00:00	17:00:00

And Output is

Date	Name	Shift in	Shift Out	NEXT IN	NEXT OUT
01-04-18	A	7:00:00	13:00:00	8:00:00	14:00:00
02-04-18	A	8:00:00	14:00:00	9:00:00	15:00:00
03-04-18	A	9:00:00	15:00:00	10:00:00	16:00:00
04-04-18	A	10:00:00	16:00:00	11:00:00	17:00:00
05-04-18	A	11:00:00	17:00:00
01-04-18	B	7:00:00	13:00:00	8:00:00	14:00:00
02-04-18	B	8:00:00	14:00:00	9:00:00	15:00:00
03-04-18	B	9:00:00	15:00:00	10:00:00	16:00:00
04-04-18	B	10:00:00	16:00:00	11:00:00	17:00:00
05-04-18	B	11:00:00	17:00:00
01-04-18	C	7:00:00	13:00:00	8:00:00	14:00:00
02-04-18	C	8:00:00	14:00:00	9:00:00	15:00:00
03-04-18	C	9:00:00	15:00:00	10:00:00	16:00:00
04-04-18	C	10:00:00	16:00:00	11:00:00	17:00:00
05-04-18	C	11:00:00	17:00:00
01-04-18	D	7:00:00	13:00:00	8:00:00	14:00:00
02-04-18	D	8:00:00	14:00:00	9:00:00	15:00:00
03-04-18	D	9:00:00	15:00:00	10:00:00	16:00:00
04-04-18	D	10:00:00	16:00:00	11:00:00	17:00:00
05-04-18	D	11:00:00	17:00:00
01-04-18	E	7:00:00	13:00:00	8:00:00	14:00:00
02-04-18	E	8:00:00	14:00:00	9:00:00	15:00:00
03-04-18	E	9:00:00	15:00:00	10:00:00	16:00:00
04-04-18	E	10:00:00	16:00:00	11:00:00	17:00:00
05-04-18	E	11:00:00	17:00:00

KINDLY TELL ME HOW TO USE PEEK WITH IF STATEMENT IN ABOVE REQUIREMENT

For This i am attaching excel file for row data in sheet 1

and

sheet 2 is result part

michal__ · ‎2019-02-09

Hi,

try this script.

NoConcatenate
[NEXT_SHIFT_TMP]:
LOAD
   "Date"
    ,Name
   ,"Shift in"
    ,"Shift Out"
    ,If( Previous(Name) = Name, Peek("Shift in"), Null() )   AS [NEXT IN]
    ,If( Previous(Name) = Name, Peek("Shift Out"), Null() )   AS [NEXT OUT]
RESIDENT
   [SHIFTS_TMP]
ORDER BY
   Name
    ,Date desc
;

GauravKumar1 · ‎2019-02-10

Kindly send the qvw....
When i am using this code then not working...

michal__ · ‎2019-02-10

Hi,

please find the complete script below.

[SHIFTS_TMP]:
LOAD
    "Date",
    Name,
    "Shift in",
    "Shift Out"
FROM [lib://Qlik Community/Data.xlsx]
(ooxml, embedded labels, table is Sheet1);

NoConcatenate
[NEXT_SHIFT_TMP]:
LOAD
   "Date"
    ,Name
   ,"Shift in"
    ,"Shift Out"
    ,If( Previous(Name) = Name, Peek("Shift in"), Null() )   AS [NEXT IN]
    ,If( Previous(Name) = Name, Peek("Shift Out"), Null() )   AS [NEXT OUT]
RESIDENT
   [SHIFTS_TMP]
ORDER BY
   Name
    ,Date desc
;

Drop Table SHIFTS_TMP;

NoConcatenate
[SHIFTS]:
LOAD
   *
RESIDENT
   NEXT_SHIFT_TMP
ORDER BY
   Name
    ,Date
;

Drop Table NEXT_SHIFT_TMP;

Warning: You have to adjust source path in first load statement.

If you have any questions, please don't hesitate to contact me.

GauravKumar1 · ‎2019-02-11

I am attaching the data.kindly see this and apply this code.

Please help me its very urgent

GauravKumar1 · ‎2019-02-11

Dear sir,
Kindly tell me....how to use this logic above attached data

GauravKumar1 · ‎2019-02-12

What Happened

michal__ · ‎2019-02-12

Hi,

i have already posted complete script that do the job.

Do you have any errors during its execution?

GauravKumar1 · ‎2019-02-13

Dear Sir,

i attached the data in above.when i am using this code.then it's not working.again i am attaching data.

michal__ · ‎2019-02-15

Hi,

did you mange to apply the script?