Dynamic peek

[miikkaqlick]

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

[marcus_sommer]

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

- Marcus

[miikkaqlick]

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]

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.

[miikkaqlick]

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 · Accepted solution]

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]

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