Load script

[Sharbel]

Hello,

I have the following table that contains Time log for certain employee:

emp	Date	Time	Code
1995	26/10/2024	06:59	100
1995	26/10/2024	14:49	200
1995	26/10/2024	23:01	100
1995	27/10/2024	07:22	200
1995	27/10/2024	14:56	100
1995	27/10/2024	23:07	200
1995	28/10/2024	07:01	100
1995	28/10/2024	15:01	200
1995	29/10/2024	07:15	100

emp = employee number 

100 = time in , 200 = time out

i am trying to write Load script that transforms the above table to the following :

emp	Time_In	Time_out
1995	06:59	14:49
1995	23:01	07:22
1995	14:56	23:07
1995	07:01	15:01
1995	07:15	-

any ideas?

Regards,

Sharbel

[maxgro]

An idea could be

// load your data (modify if you use Qlik Sense)

tmp:

LOAD emp, 

     Date, 

     Time, 

     Code

FROM

[https://community.qlik.com/t5/App-Development/Load-script/td-p/2489708]

(html, utf8, UserAgent is 'Mozilla/5.0', embedded labels, table is @1);

// add to every row the time of the previous row

// load order by ....

tmp2:

load 

emp,

Date,

Time,

Code,

IF(Code=100 and Peek(Code)=200 and emp=peek(emp), peek(Time)) as PrevTime1,

peek(Time) as PrevTime 

Resident tmp

order by emp, Date desc, Time desc;

drop table tmp;

// delete rows 

tmp3:

noconcatenate

load * resident tmp2

where len(trim(PrevTime1)) > 0;

drop table tmp2; 

[Kushal_Chawda · Accepted solution]

@Sharbel  one more option. Replace Inline load with actual source.

Data:
Load * Inline [
emp	Date	Time	Code
1995	26/10/2024	06:59	100
1995	26/10/2024	14:49	200
1995	26/10/2024	23:01	100
1995	27/10/2024	07:22	200
1995	27/10/2024	14:56	100
1995	27/10/2024	23:07	200
1995	28/10/2024	07:01	100
1995	28/10/2024	15:01	200
1995	29/10/2024	07:15	100

1996	26/10/2024	05:59	100
1996	26/10/2024	13:49	200
1996	26/10/2024	22:01	100
1996	27/10/2024	06:22	200
](delimiter is '\t');

Sort:
NoConcatenate
Load  *,
     if(emp=Previous(emp),Previous(Time)) as Out_Time,
     if(emp=Previous(emp),Previous(Date)) as Out_Date
Resident Data
Order by emp,Date desc,Time desc;

Drop Table Data;

Final:
NoConcatenate
Load *
Resident Sort
where Code=100;

Drop Table Sort;

Drop Fields Code;

Rename Field Time to In_Time;
Rename Field Date to In_Date;

[Sharbel · Accepted solution]

I ran the script and it works ! 

thanks @Kushal_Chawda 

[Sharbel]

Hi @maxgro 

i ran the scriptand got the following result: 

note that the row with the entry date 28/10/2024 , entry time 23:05 is missing

[seanbruton]

Superb !!! Neat Solution.