4 Replies Latest reply: Aug 2, 2018 7:08 AM by omar bensalem RSS

    Help required

    omar bensalem

      Hi guys ( stalwar1  petter-s jontydkpi )

       

      Would please help with the follow?

       

      Suppose I have this:

       

      load * inline [

      id, Demande, Publication, Candidature,Creation

      1, 15/01/2017, , 03/01/2017,10/12/2016

      2, 12/03/2016, 15/01/2015, , 01/03/2014

      3, 05/03/2017, 04/03/2017, 06/03/2017, 05/01/2015

      ];

       

      What I want to achieve is :

      id ,action0,action1,action2,action3

      1, Creation, Candidature, Demande,

      2, Creation, Publication, Demande,

      3, Creation , Publication, Demande, Candidature

       

       

      It's based on the Date

       

      Here's what I did:

       

      cross:

      CrossTable(Action,Date) load * Inline [

      id, Demande, Publication, Candidature,Creation

      1, 15/01/2017, , 03/01/2017,10/12/2016

      2, 12/03/2016, 15/01/2015, , 01/03/2014

      3, 05/03/2017, 04/03/2017, 06/03/2017, 05/01/2015

      ];

       

      NoConcatenate

      t0:

      load id, Action ,Date Resident cross  Where id='1' and  len(trim(Date))<>0 order by id,Date ;

       

      drop Table cross;

       

       

      final:

      load  id Resident t0;

       

      for i=0 to NoOfRows('t0')-1

      let vAction$(i)= Peek('Action',$(i),'t0');

      let vRow= Peek('id',$(i),'t0');

       

      load Distinct id,  '$(vAction$(i))' as "Action$(i)"

      Resident t0;

      next i

       

       

      drop Table t0;



      This works perfectly if there was only ONE id..

       

      Capture.PNG

       

      But if there were more than one ID, the script will be executing and we'll have so many actions since the loop will go on to the last row..

       

       

       

      Capture.PNG

       

      The question is, how can this treatment be seperated by id?

      I mean,I treat every id apart then concatenate the whole thing?

       

      Thanks a lot !

        • Re: Help required
          Sunny Talwar

          Try this

           

          cross:

          CrossTable(Action, Date)

          LOAD * INLINE [

              id, Demande, Publication, Candidature, Creation

              1, 15/01/2017, , 03/01/2017, 10/12/2016

              2, 12/03/2016, 15/01/2015, , 01/03/2014

              3, 05/03/2017, 04/03/2017, 06/03/2017, 05/01/2015

          ];


          Table:

          NoConcatenate

          LOAD id,

          Action,

          Date,

          If(id = Previous(id), RangeSum(Peek('Num'), 1), 0) as Num

          Resident cross

          Where Len(Trim(Date)) > 0

          Order By id, Date;


          DROP Table cross;


          FinalTable:

          LOAD Distinct id

          Resident Table;


          FOR i = 1 to FieldValueCount('Num')


          LET vNum = FieldValue('Num', $(i));

           

          Left Join (FinalTable)

          LOAD id,

          Action as [action$(vNum)]

          Resident Table

          Where Num = '$(vNum)';


          NEXT


          DROP Table Table;

            • Re: Help required
              omar bensalem

              You; my friend... you're The Genius !

              I just love you

              • Re: Help required
                omar bensalem

                One stupid question though, how can we begin with Action1 instead of Action0?

                  • Re: Help required
                    Sunny Talwar

                    Change 0 to 1 here

                     

                    cross:

                    CrossTable(Action, Date)

                    LOAD * INLINE [

                        id, Demande, Publication, Candidature, Creation

                        1, 15/01/2017, , 03/01/2017, 10/12/2016

                        2, 12/03/2016, 15/01/2015, , 01/03/2014

                        3, 05/03/2017, 04/03/2017, 06/03/2017, 05/01/2015

                    ];


                    Table:

                    NoConcatenate

                    LOAD id,

                    Action,

                    Date,

                    If(id = Previous(id), RangeSum(Peek('Num'), 1), 0) as Num

                    Resident cross

                    Where Len(Trim(Date)) > 0

                    Order By id, Date;


                    DROP Table cross;


                    FinalTable:

                    LOAD Distinct id

                    Resident Table;


                    FOR i = 1 to FieldValueCount('Num')


                    LET vNum = FieldValue('Num', $(i));

                     

                    Left Join (FinalTable)

                    LOAD id,

                    Action as [action$(vNum)]

                    Resident Table

                    Where Num = '$(vNum)';


                    NEXT


                    DROP Table Table;