18 Replies Latest reply: Jul 20, 2018 10:01 AM by omar bensalem RSS

    Help with Logic

    omar bensalem

      Hi all ( stalwar1)

      I have a question:

       

      I have sthing like this:

      Date1,2 and 3; and for every ID, I want to create a rank field, to rank the dates in ascending order; is there a way to do this? without long if else..?

      and one remark: NOT ALL DATES are always there; u can find many IDs with one or many missing dates.

       

      IDDate1Date2Date3Rank
      117/06/201715/01/201815/11/2016Date3 - Date1- Date2
        • Re: Help with Logic
          Sunny Talwar

          May be cross table and then Concatenate them back with sort weight of Date

            • Re: Help with Logic
              omar bensalem

              The idea is to have sthing like this :

              Capture.PNG

               

              where we track the chrnological order of the actions based on their Starting Date:

               

              if I select Action 1 : Publication:

               

              I'll have this: I'll know those who had as first action: publication , what was their second one, action 3 and so on..

               

              Capture.PNG

               

              But to achieve this; I've had a HUGE if else script: H.U.G.E;

              it works just fine; BUT, if i add yet another action in my logic; ALL THE SCRIPT must change with so MANY combinations.. I just don't want to think about it ...

               

              Do u see any easier way to achieve this sunny?

                • Re: Help with Logic
                  Marcus Sommer

                  If you used a subfield-load after the crosstable-concat suggestion from Sunny you would get your Rank field. Maybe easier than that would be to use range-functions like:

                   

                  rangemin(Date1, Date2, Date3) & '-' &

                  rangeavg(Date1, Date2, Date3) & '-' &

                  rangemax(Date1, Date2, Date3) as Rank

                   

                  - Marcus

                    • Re: Help with Logic
                      Sunny Talwar

                      Will RangeAvg() work here? I have a feeling it might not... but may be I am wrong.... I would have thought that may be RangeFractile will make more sense...

                       

                      Update... something like this

                       

                      Date(RangeMin(Date1, Date2, Date3)) & '-' &

                      Date(RangeFractile(0.5, Date1, Date2, Date3)) & '-' &

                      Date(RangeMax(Date1, Date2, Date3)) as Rank1

                        • Re: Help with Logic
                          Marcus Sommer

                          As far as there are only and always 3 different dates it should work. If not there might be some additionally measures necessary like checking the number of dates with rangecount() and branching then per if-then-else into different branches.

                           

                          This means it will depend on the real data ... whereby I assume that even by multiple nested if-loops the performance is better as with the crosstable + aggregation load.

                           

                          - Marcus

                      • Re: Help with Logic
                        Sunny Talwar

                        I don't know if you like this or not, but try this

                         

                        Table:

                        LOAD * INLINE [

                            ID, Date1, Date2, Date3

                            1, 17/06/2017, 15/01/2018, 15/11/2016

                        ];


                        CrossTable:

                        CrossTable (DateType, Date)

                        LOAD *

                        Resident Table;


                        Left Join (Table)

                        LOAD ID,

                        Concat(DateType, ' - ', Date) as Rank

                        Resident CrossTable

                        Group By ID;


                        Drop Table CrossTable;

                          • Re: Help with Logic
                            Sunny Talwar

                            For concatenating the actual date... you can use this

                             

                            Table:

                            LOAD * INLINE [

                                ID, Date1, Date2, Date3

                                1, 17/06/2017, 15/01/2018, 15/11/2016

                            ];


                            CrossTable:

                            CrossTable (DateType, Date)

                            LOAD *

                            Resident Table;


                            Left Join (Table)

                            LOAD ID,

                            Concat(Date, ' - ', Date) as Rank

                            Resident CrossTable

                            Group By ID;


                            Drop Table CrossTable;

                      • Re: Help with Logic
                        omar bensalem

                        Thanks to everyone ! I'll try ur suggestions tomorrow; but u'd have to know that I'd need a generic script that works whatever date I add; because right now, i have 4 not 3 dates and 2 more dates could be added..

                        thus, I don't think the avg() would work in this case

                          • Re: Help with Logic
                            Sunny Talwar

                            I think CrossTable might be the most generic solution you can get... but I would def. take into consideration Marcus's responses

                            • Re: Help with Logic
                              Marcus Sommer

                              The most generic way would be the suggestion from Sunny and if there are no serious performance impacts I would probably go with it.

                               

                              But it could be also resolved on a record-level with a comparing from each value against each value. This may seem a quite heavy scripting with many if-loops but it could also be done more elegant - and I think the following could be even more optimized maybe by using some (parametrized) variables or a better algorithm - and you don't need to split it in respectively keep all intermediate steps:

                               

                              t1:
                              load
                              *,
                              subfield(D, '|', index(RD, 1)) &'-'&
                              subfield(D, '|', index(RD, 2)) &'-'&
                              subfield(D, '|', index(RD, 3)) &'-'&
                              subfield(D, '|', index(RD, 4)) &'-'&
                              subfield(D, '|', index(RD, 5)) &'-'&
                              subfield(D, '|', index(RD, 6)) as Rank;
                              load
                              *,
                              (
                              rangesum(-(D1>D2),-(D1>D3),-(D1>D4),-(D1>D5),-(D1>D6))-6)*-1 as RD1,
                              (
                              rangesum(-(D2>D1),-(D2>D3),-(D2>D4),-(D2>D5),-(D2>D6))-6)*-1 as RD2,
                              (
                              rangesum(-(D3>D1),-(D3>D2),-(D3>D4),-(D3>D5),-(D3>D6))-6)*-1 as RD3,
                              (
                              rangesum(-(D4>D1),-(D4>D2),-(D4>D3),-(D4>D5),-(D4>D6))-6)*-1 as RD4,
                              (
                              rangesum(-(D5>D1),-(D5>D2),-(D5>D3),-(D5>D4),-(D5>D6))-6)*-1 as RD5,
                              (
                              rangesum(-(D6>D1),-(D6>D2),-(D6>D3),-(D6>D4),-(D6>D5))-6)*-1 as RD6,
                              (
                              rangesum(-(D1>D2),-(D1>D3),-(D1>D4),-(D1>D5),-(D1>D6))-6)*-1 &
                              (
                              rangesum(-(D2>D1),-(D2>D3),-(D2>D4),-(D2>D5),-(D2>D6))-6)*-1 &
                              (
                              rangesum(-(D3>D1),-(D3>D2),-(D3>D4),-(D3>D5),-(D3>D6))-6)*-1 &
                              (
                              rangesum(-(D4>D1),-(D4>D2),-(D4>D3),-(D4>D5),-(D4>D6))-6)*-1 &
                              (
                              rangesum(-(D5>D1),-(D5>D2),-(D5>D3),-(D5>D4),-(D5>D6))-6)*-1 &
                              (
                              rangesum(-(D6>D1),-(D6>D2),-(D6>D3),-(D6>D4),-(D6>D5))-6)*-1 as RD
                              ;
                              load *, recno() as ID, D1&'|'&D2&'|'&D3&'|'&D4&'|'&D5&'|'&D6 as D inline [
                              D1   D2   D3   D4   D5   D6
                              26.04.2018 10.07.2017 12.09.2015 30.04.2017 12.04.2017 24.05.2016
                              02.09.2016 19.04.2016 24.07.2017 05.02.2018 10.03.2016 19.06.2018
                              15.09.2015 31.01.2016 17.01.2018 23.09.2015 03.09.2016 24.12.2017
                              30.01.2018 10.11.2017 31.07.2017 22.07.2016 27.05.2017 06.12.2015
                              19.09.2017 17.04.2017 21.04.2017 18.12.2016 11.07.2016 17.10.2016
                              28.01.2018 29.04.2016 09.03.2018 12.08.2017 17.04.2016 22.05.2016
                              18.06.2017 17.04.2017 29.01.2018 26.04.2018 04.02.2016 30.04.2017
                              17.11.2015 12.01.2016 28.10.2015 15.11.2016 18.09.2016 10.07.2018
                              07.01.2018 05.02.2018 12.03.2018 26.10.2016 19.03.2017 28.05.2018
                              11.01.2017 18.11.2016 17.09.2016 16.06.2018 13.09.2016 22.01.2016
                              09.07.2018 16.08.2015 15.11.2016 14.08.2016 26.03.2018 08.12.2017
                              02.02.2017 24.03.2016 15.02.2017 10.05.2017 19.07.2016 15.03.2017
                              07.08.2015 15.07.2018 29.11.2016 30.06.2018 26.06.2017 08.02.2018
                              10.07.2016 30.12.2015 10.04.2017 24.08.2015 26.12.2016 22.03.2016
                              27.05.2017 16.04.2018 01.09.2016 23.06.2018 24.04.2018 12.08.2017
                              17.11.2017 21.01.2017 06.12.2017 20.08.2016 16.09.2016 06.01.2018
                              06.01.2016 03.04.2016 11.12.2015 18.02.2017 16.08.2015 22.02.2018
                              20.04.2018 09.07.2016 28.11.2017 06.04.2016 08.12.2015 13.01.2017
                              12.09.2016 26.02.2017 30.01.2017 09.05.2017 14.02.2017 21.04.2016
                              01.09.2017 03.09.2016 22.04.2016 17.03.2018 22.11.2017 31.03.2016
                              ]
                              (txt, delimiter is
                              \t);

                               

                              - Marcus

                            • Re: Help with Logic
                              kaan erisen

                              Hi Omar,

                               

                              how about this? Is this what you are looking for?

                               

                              RawData:

                              Load * Inline [

                              ID,Date1,Date2,Date3

                              1,17/06/2017,15/01/2018,15/11/2016

                              2,17/05/2017,15/08/2018,15/11/2019

                              ];

                               

                              temp:

                              CrossTable(DateType,Date,1)

                              load * Resident RawData;

                               

                              left join (RawData)

                              Result:

                              load ID,Concat(DateType,'-',Date#(Date,'DD/MM/YYYY')) as Rank Resident temp group by ID;

                               

                              drop table temp;

                               

                              Untitled.png

                               

                              Hope it helps.