Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
er_mohit
Master II
Master II

how to peek field values on the basis of comparision

hii guys. i tried a lot for getting the time between  but can't get success on script side

TAKE AN EXAMPLE....

i have one table

A:

LOAD PAYCODE,

TR_LUNCHSTARTTIME,

   TR_LUNCHENDTIME,

ACYUALIN,ACTUALOUT

FROM TABLE;

JOIN(A)

LOAD CARDNO as PAYCODE,

TIME(OFFICEPUNCH)AS MOVMENT

from table2;

there i want a MOVMENT i.e my time field and i want to peek that values from MOVMENT which are greater than ACTUALIN AND Less than LUNCHSTARTTIME

(min and max)

after that i want also peek those values which are greater than TR_LUNCHENDTIME and less than ACTUALOUT

HAVE AN EXAMPLE I HAVE MOVMENT OF PAYCODE

MOVMENT
08:43:00
10:58:00
13:17:00
18:13:00

and

ACTUALIN
08:43:00

and

ACTUALOUT
18:13:00

now i would like in pivot table

10:58:00

13:17:00

here,lunch time is 1:00:00 to 2:00:00,if in MOVMENT GETTING two times between LUNCHSTARTTIME & LUNCHENDTIME

so, i didnt want the values of MOVMENT between them....

like

MOVMENT
08:43:00
10:58:00

13:17:00

13:47:00

18:13:00

there i want only 10:58:00 -

thanks in advance.......

1 Solution

Accepted Solutions
Gysbert_Wassenaar

Maybe like this. See attached example.

T1:

LOAD *, if(ID=previous(ID),0,1) as fStartTime,

if(TIME<=MakeTime(14,00) and TIME>=maketime(13,00) ,1,0) as fInBreak1,

if(TIME<=MakeTime(14,00) and TIME>=maketime(13,00) and previous(TIME)<=MakeTime(14,00) and previous(TIME)>=maketime(13,00),1,0) as fInBreak2

INLINE [

    ID, TIME

    1, 08:43

    1, 10:58

    1, 13:17

    1, 13:47

    1, 18:13

    2, 09:03

    2, 13:45

    2, 18:23

    3, 08:56

    3, 13:11

    3, 14:23

    3, 17:56

];

 

T2:

load *,

if(ID=previous(ID),0,1) as fEndTime,

if(fInBreak2=1 or (fInBreak1=1 and previous(fInBreak2)=1),1,0) as fInBreak3

Resident T1 where fStartTime=0 order by ID,TIME desc;

 

drop table T1;

 

T3:

load ID,TIME

Resident T2 where fInBreak3 =0 and fEndTime=0;

 

drop table T2;


talk is cheap, supply exceeds demand

View solution in original post

4 Replies
er_mohit
Master II
Master II
Author

help meeeeeeeeeeeeeeeeeeeee

plzzzzzzzzzzzzzzzz

Gysbert_Wassenaar

Maybe like this. See attached example.

T1:

LOAD *, if(ID=previous(ID),0,1) as fStartTime,

if(TIME<=MakeTime(14,00) and TIME>=maketime(13,00) ,1,0) as fInBreak1,

if(TIME<=MakeTime(14,00) and TIME>=maketime(13,00) and previous(TIME)<=MakeTime(14,00) and previous(TIME)>=maketime(13,00),1,0) as fInBreak2

INLINE [

    ID, TIME

    1, 08:43

    1, 10:58

    1, 13:17

    1, 13:47

    1, 18:13

    2, 09:03

    2, 13:45

    2, 18:23

    3, 08:56

    3, 13:11

    3, 14:23

    3, 17:56

];

 

T2:

load *,

if(ID=previous(ID),0,1) as fEndTime,

if(fInBreak2=1 or (fInBreak1=1 and previous(fInBreak2)=1),1,0) as fInBreak3

Resident T1 where fStartTime=0 order by ID,TIME desc;

 

drop table T1;

 

T3:

load ID,TIME

Resident T2 where fInBreak3 =0 and fEndTime=0;

 

drop table T2;


talk is cheap, supply exceeds demand
er_mohit
Master II
Master II
Author

hiii Gysbert Wassenaar

i can't open your application because i use personal edition.. could you please explain me

er_mohit
Master II
Master II
Author

HIII

in as your above script is superb work for me but according to my script how achive it

see the attached file ther i attached my scripting.i can't share personal data of my company