1 Reply Latest reply: Nov 10, 2015 5:46 AM by Stefan Wühl RSS

    Handling two profiles for a cutomer ID

    reddy r

      Hi

       

      I have two tables

       

      Table1:
      CustomerProfileTS
      10017/28/2015 11:54
      10029/20/2015 10:42
      20039/28/2015 10:44
      20049/28/2015 10:55
      500510/28/2015 10:45
      Table2:
      CustomerStatus
      100In prog
      200Cancel
      500Completed
      Output:
      ProfileStatus
      1Error
      2In Prog
      3Error
      4Cancel
      5Completed

      In the above Scenario:

      If the customer has two profiles for EX:100 have two profiles 1 and 2
      but we need to show old profile status as error and for the new one Status should shown as per table 2,(which means the profile1 has an error so we are using profile2 that we need to display in our output)

      if the customer has one profile, status should shown as per table 2:

        • Re: Handling two profiles for a cutomer ID
          Stefan Wühl

          Maybe like this:

           

          SET TimestampFormat='M/D/YYYY h:mm';

           

          Table1:

          LOAD * INLINE [

          Customer, Profile, TS

          100, 1, 7/28/2015 11:54

          100, 2, 9/20/2015 10:42

          200, 3, 9/28/2015 10:44

          200, 4, 9/28/2015 10:55

          500, 5, 10/28/2015 10:45

          ];

           

           

          Table2:

          LEFT JOIN

          LOAD * INLINE [

          Customer, Status

          100, In prog

          200, Cancel

          500, Completed

          ];

           

           

          NoConcatenate

          LOAD Customer,

            Profile,

            TS,

            if(Peek(Customer)=Customer, 'Error',Status) as Status

          Resident Table1

          ORDER BY Customer, TS Desc;

           

           

          DROP Table Table1;