Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
miikkaqlick
Partner - Creator II
Partner - Creator II

Dynamic peek

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

1 Solution

Accepted Solutions
maxgro
MVP
MVP

1.png

Y:

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;

View solution in original post

8 Replies
marcus_sommer

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

- Marcus

miikkaqlick
Partner - Creator II
Partner - Creator II
Author

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.

marcus_sommer

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

jonathandienst
Partner - Champion III
Partner - Champion III

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
miikkaqlick
Partner - Creator II
Partner - Creator II
Author

Hi!

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

Br,

Miikka

marcus_sommer

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

maxgro
MVP
MVP

1.png

Y:

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;

miikkaqlick
Partner - Creator II
Partner - Creator II
Author

Thanks!

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

Y:

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