Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
GauravKumar1
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
michal__
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
;

GauravKumar1
Contributor II
Contributor II
Author

Kindly send the qvw....
When i am using this code then not working...
michal__
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.

 

GauravKumar1
Contributor II
Contributor II
Author

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

Please help me its very urgent

GauravKumar1
Contributor II
Contributor II
Author

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

What Happened

michal__
Contributor III
Contributor III

Hi,

i have already posted complete script that do the job.

Do you have any errors during its execution?

GauravKumar1
Contributor II
Contributor II
Author

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__
Contributor III
Contributor III

Hi,

did you mange to apply the script?