topic Dynamic peek in QlikView

Dynamic peek

miikkaqlick — Tue, 01 Nov 2016 11:03:38 GMT

Hi!

I have personnel data and I should created dynamic peek. My data is:

id, Person, Start, End, Code

1, AA_AA, 20160220, 20160225, J

2, AA_AA, 20160227, 20160310, J

3, BB_BB, 20160227, 20160330, M

4, AA_AA, 20160312, 20160315, J

5, AA_AA, 20160214, 20160218, M

6, AA_AA, 20161112, 20161123, J

7, BB_BB, 20160410, 20160412, M

If person is same, code is same and previous start is less than 30 days from next start I should get first start. In this data id's 1,2,4 and 3,7 fills conditions.

So I need line like:

Person, Start, End, Code

AA_AA, 20160220, 20160315, J

BB_BB, 20160227, 20160412, M

ID 5 has different code and ID 6 is too far.

For some reason I can't figure correct syntax for Peek.

Ideas?

Br,

Miikka

Re: Dynamic peek

marcus_sommer — Tue, 01 Nov 2016 13:03:00 GMT

Have you ordered your load with a order by Person, Start; ?

- Marcus

Re: Dynamic peek

miikkaqlick — Tue, 01 Nov 2016 13:45:16 GMT

Thanks for reply!

Yes I have. I'm able to come up with lines:

Key, Start, End

AA_AA-J, 20160220, 20160310

AA_AA-J, 20160310, 20160315

BB_BB-M, 20160227, 20160412

But that's not correct solution and in actual data there is unknown amount of rows. So I can Peek one line up, but not dynamic lines up.

Re: Dynamic peek

marcus_sommer — Tue, 01 Nov 2016 14:02:04 GMT

I'm not quite sure about your logic how the records should be denoted but I would try something like this:

t1:

load *, Person & '-' & Code as Key, date(date#(Start, 'YYYYMMDD')) as Start1 inline [

id, Person, Start, End, Code

1, AA_AA, 20160220, 20160225, J

2, AA_AA, 20160227, 20160310, J

3, BB_BB, 20160227, 20160330, M

4, AA_AA, 20160312, 20160315, J

5, AA_AA, 20160214, 20160218, M

6, AA_AA, 20161112, 20161123, J

7, BB_BB, 20160410, 20160412, M

];

t2:

load *, rowno() as RowNo resident t1 order by Key, Start;

t3:

load *, if(Key = previous(Key) and Start1 - previous(Start1) < 30, 1, 0) as RecordFlag resident t2;

drop tables t1, t2;

- Marcus

Re: Dynamic peek

jonathandienst — Tue, 01 Nov 2016 14:03:20 GMT

Make sure to convert the start and end fields to proper date values so the date arithmetic works correctly. Something like:

T_Data:

LOAD id,

Person,

Date#(Start, 'yyyyMMdd') as Start,

Date#(End, 'yyyyMMdd') as End,

Code,

FROM ...

Result:

LOAD id,

Person,

Start,

End,

Code,

If(Person = Previous(Person) And Code = Previous(Code) And Start - Peek(LastStart) < 30, 1, 0) as Flag,

If(Person = Previous(Person) And Code = Previous(Code) And Start - Peek(LastStart) < 30, Peek(LastStart), Start) as LastStart

Resident T_Data

Order By

Person,

Start;

Drop Table T_Data;

Now use the Flag field to control what to display. Adjust the script to your precise requirements and correct any minor syntax errors which creep in when writing abstract code.

Re: Dynamic peek

miikkaqlick — Tue, 01 Nov 2016 14:35:44 GMT

Hi!

Unfortunately both solutions are wrong. I've attached sample qvw with explanation and my own try.

Br,

Miikka

Re: Dynamic peek

marcus_sommer — Tue, 01 Nov 2016 14:51:01 GMT

Like above mentioned it's not quite clear for me what do you want to achieve - if you want also to flag the initial-record you could use a further and opposite running peek-load like in this example:

t4:

load *, if(previous(RecordFlag) = 1 or RecordFlag = 1, 1, 0) as RecordFlag2 resident t3 order by Key desc;

// maybe with splitting the loop-logic to differentiate both conditions

drop tables t1, t2, t3;

But your matching from ID's 3 and 7 didn't fit to your conditions because they are not within 30 days.

- Marcus

Re: Dynamic peek

maxgro — Tue, 01 Nov 2016 15:01:04 GMT

load * inline [

id, Person, Start, End, Code

1, AA_AA, 20160220, 20160225, J

2, AA_AA, 20160227, 20160310, J

3, BB_BB, 20160227, 20160330, M

4, AA_AA, 20160312, 20160315, J

5, AA_AA, 20160214, 20160218, M

6, AA_AA, 20161112, 20161123, J

7, BB_BB, 20160410, 20160412, M

];

YY:

NoConcatenate

load

if(Person = Peek('Person') and Code = Peek('Code') and Date#(Start, 'YYYYMMDD') <= ( date#(Peek('NewEnd'), 'YYYYMMDD')+30), peek('NewEnd'), End) as NewEnd,

if(Person = Peek('Person') and Code = Peek('Code') and Date#(Start, 'YYYYMMDD') <= ( date#(Peek('NewEnd'), 'YYYYMMDD')+30), peek('NewStart'), Start) as NewStart,

if(Person = Peek('Person') and Code = Peek('Code') and Date#(Start, 'YYYYMMDD') <= ( date#(Peek('NewEnd'), 'YYYYMMDD')+30), 1, 0) as Flag

resident Y

Order By Person, Code, Start;

DROP Table Y;

YYY:

LOAD Person, Code, Start, End

Where cnt > 1;

load

Person, Code,

date(min(Start)) as Start,

date(max(End)) as End,

count(id) as cnt

Resident YY

Group by Person, Code, NewStart, NewEnd;

DROP Table YY;

Re: Dynamic peek

miikkaqlick — Tue, 01 Nov 2016 16:23:59 GMT

Thanks!

I added date transformation to make code more readable nut your solution is correct:)

load * inline [

id, Person, Start, End, Code

1, AA_AA, 20160220, 20160225, J

2, AA_AA, 20160227, 20160310, J

3, BB_BB, 20160227, 20160330, M

4, AA_AA, 20160312, 20160315, J

5, AA_AA, 20160214, 20160218, M

6, AA_AA, 20161112, 20161123, J

7, BB_BB, 20160410, 20160412, M

];

YZ:

Load

Num(Date#(Start, 'YYYYMMDD')) as StartNum,

Num(Date#(End,'YYYYMMDD')) as EndNum,

Person &'-'& Code as key

Resident Y;

YY:

NoConcatenate

load

if(key = Peek('key') and StartNum <= ( Peek('NewEnd')+30), peek('NewEnd'), EndNum) as NewEnd,

if(key = Peek('key') and StartNum <= ( Peek('NewEnd')+30), peek('NewStart'), StartNum) as NewStart,

if(key = Peek('key') and StartNum <= ( Peek('NewEnd')+30), 1, 0) as Flag

resident YZ

Order By Person, Code, Start;

DROP Table Y, YZ;

YYY:

LOAD Person, Code, Start, End

Where cnt > 1;

load

Person,

Code,

date(min(Start)) as Start,

date(max(End)) as End,

count(id) as cnt

Resident YY

Group by Person, Code, NewStart, NewEnd;

DROP Table YY;

Br,

Miikka