Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Highlighted
GauravKumar1
New 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
michal__
New Contributor II

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
;

GauravKumar1
New Contributor II

Re: How to create next row in script

Kindly send the qvw....
When i am using this code then not working...
michal__
New Contributor II

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.

 

GauravKumar1
New 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

GauravKumar1
New Contributor II

Re: How to create next row in script

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

Re: How to create next row in script

What Happened

michal__
New Contributor II

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?

GauravKumar1
New 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.

 

michal__
New Contributor II

Re: How to create next row in script

Hi,

did you mange to apply the script?