topic Re: Load script in App Development

Load script

Sharbel — Tue, 29 Oct 2024 13:26:38 GMT

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

Re: Load script

maxgro — Tue, 29 Oct 2024 15:25:41 GMT

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;

Re: Load script

Kushal_Chawda — Tue, 29 Oct 2024 15:53:54 GMT

@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;

Re: Load script

Sharbel — Fri, 01 Nov 2024 09:15:44 GMT

I ran the script and it works !

thanks @Kushal_Chawda

Re: Load script

Sharbel — Fri, 01 Nov 2024 09:25:41 GMT

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

Re: Load script

seanbruton — Fri, 01 Nov 2024 15:43:21 GMT

Superb !!! Neat Solution.