8 Replies Latest reply: Nov 1, 2016 12:23 PM by Miikka Koskinen RSS

    Dynamic peek

    Miikka Koskinen

      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

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

           

          - Marcus

            • Re: Dynamic peek
              Miikka Koskinen

              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

                  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
                    Jonathan Dienst

                    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
                        Miikka Koskinen

                        Hi!

                         

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

                         

                        Br,

                        Miikka

                          • Re: Dynamic peek
                            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

                    • Re: Dynamic peek
                      Massimo Grossi

                      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;

                        • Re: Dynamic peek
                          Miikka Koskinen

                          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