topic Re: How to create next row in script in QlikView

How to create next row in script

GauravKumar1 — Sat, 16 Nov 2024 21:26:36 GMT

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

Re: How to create next row in script

michal__ — Sat, 09 Feb 2019 19:44:13 GMT

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
;

Re: How to create next row in script

GauravKumar1 — Sun, 10 Feb 2019 14:49:05 GMT

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

Re: How to create next row in script

michal__ — Sun, 10 Feb 2019 16:41:35 GMT

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.

Re: How to create next row in script

GauravKumar1 — Mon, 11 Feb 2019 09:55:06 GMT

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

Please help me its very urgent

Re: How to create next row in script

GauravKumar1 — Mon, 11 Feb 2019 16:09:15 GMT

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

Re: How to create next row in script

GauravKumar1 — Tue, 12 Feb 2019 14:12:42 GMT

What Happened

Re: How to create next row in script

michal__ — Tue, 12 Feb 2019 17:22:12 GMT

Hi,

i have already posted complete script that do the job.

Do you have any errors during its execution?

Re: How to create next row in script

GauravKumar1 — Wed, 13 Feb 2019 05:17:14 GMT

Dear Sir,

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

Re: How to create next row in script

michal__ — Fri, 15 Feb 2019 20:09:34 GMT

Hi,

did you mange to apply the script?

Re: How to create next row in script

GauravKumar1 — Wed, 20 Feb 2019 11:02:12 GMT

not working.i used this script