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

Labels (1)
10 Replies
Highlighted
Contributor III
Contributor III

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

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

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

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

Please help me its very urgent

Highlighted
Contributor II
Contributor II

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

What Happened

Highlighted
Contributor III
Contributor III

Hi,

i have already posted complete script that do the job.

Do you have any errors during its execution?

Highlighted
Contributor II
Contributor II

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

Hi,

did you mange to apply the script?