Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
er_mohit
Honored Contributor 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

Re: how to peek field values on the basis of comparision

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
4 Replies
er_mohit
Honored Contributor II

Re: how to peek field values on the basis of comparision

help meeeeeeeeeeeeeeeeeeeee

plzzzzzzzzzzzzzzzz

Re: how to peek field values on the basis of comparision

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
Honored Contributor II

Re: how to peek field values on the basis of comparision

hiii Gysbert Wassenaar

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

er_mohit
Honored Contributor II

Re: how to peek field values on the basis of comparision

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

Community Browser