Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
The #1 reason QlikView customers adopt Qlik Sense is a desire for a modern BI experience. Read More
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor II
Contributor II

How to create next row in script

Dear Team,

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

requirement is below table

 

DateNameShift inShift Out
01-04-18A7:00:0013:00:00
02-04-18A8:00:0014:00:00
03-04-18A9:00:0015:00:00
04-04-18A10:00:0016:00:00
05-04-18A11:00:0017:00:00
01-04-18B7:00:0013:00:00
02-04-18B8:00:0014:00:00
03-04-18B9:00:0015:00:00
04-04-18B10:00:0016:00:00
05-04-18B11:00:0017:00:00
01-04-18C7:00:0013:00:00
02-04-18C8:00:0014:00:00
03-04-18C9:00:0015:00:00
04-04-18C10:00:0016:00:00
05-04-18C11:00:0017:00:00
01-04-18D7:00:0013:00:00
02-04-18D8:00:0014:00:00
03-04-18D9:00:0015:00:00
04-04-18D10:00:0016:00:00
05-04-18D11:00:0017:00:00
01-04-18E7:00:0013:00:00
02-04-18E8:00:0014:00:00
03-04-18E9:00:0015:00:00
04-04-18E10:00:0016:00:00
05-04-18E11:00:0017:00:00

 

And Output is 

DateNameShift inShift OutNEXT INNEXT OUT
01-04-18A7:00:0013:00:008:00:0014:00:00
02-04-18A8:00:0014:00:009:00:0015:00:00
03-04-18A9:00:0015:00:0010:00:0016:00:00
04-04-18A10:00:0016:00:0011:00:0017:00:00
05-04-18A11:00:0017:00:00  
01-04-18B7:00:0013:00:008:00:0014:00:00
02-04-18B8:00:0014:00:009:00:0015:00:00
03-04-18B9:00:0015:00:0010:00:0016:00:00
04-04-18B10:00:0016:00:0011:00:0017:00:00
05-04-18B11:00:0017:00:00  
01-04-18C7:00:0013:00:008:00:0014:00:00
02-04-18C8:00:0014:00:009:00:0015:00:00
03-04-18C9:00:0015:00:0010:00:0016:00:00
04-04-18C10:00:0016:00:0011:00:0017:00:00
05-04-18C11:00:0017:00:00  
01-04-18D7:00:0013:00:008:00:0014:00:00
02-04-18D8:00:0014:00:009:00:0015:00:00
03-04-18D9:00:0015:00:0010:00:0016:00:00
04-04-18D10:00:0016:00:0011:00:0017:00:00
05-04-18D11:00:0017:00:00  
01-04-18E7:00:0013:00:008:00:0014:00:00
02-04-18E8:00:0014:00:009:00:0015:00:00
03-04-18E9:00:0015:00:0010:00:0016:00:00
04-04-18E10:00:0016:00:0011:00:0017:00:00
05-04-18E11:00:0017: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

Tags (1)
10 Replies
Highlighted
Contributor III
Contributor III

Re: How to create next row in script

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
;

Highlighted
Contributor II
Contributor II

Re: How to create next row in script

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

Re: How to create next row in script

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.

 

Highlighted
Contributor II
Contributor II

Re: How to create next row in script

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

Please help me its very urgent

Highlighted
Contributor II
Contributor II

Re: How to create next row in script

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

Re: How to create next row in script

What Happened

Highlighted
Contributor III
Contributor III

Re: How to create next row in script

Hi,

i have already posted complete script that do the job.

Do you have any errors during its execution?

Highlighted
Contributor II
Contributor II

Re: How to create next row in script

Dear Sir,

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

 

Highlighted
Contributor III
Contributor III

Re: How to create next row in script

Hi,

did you mange to apply the script?