2 Replies Latest reply: Apr 6, 2018 10:46 AM by sam pusala RSS

    Link Table issue

    sam pusala

      Hi,

       

      Trying to create link table to have proper associations but could't able to achieve, any help for below sample data?

       

      ORR:

       

      Load *, AutoNumberHash128(userid, acctid, orDate, orname) as %Orrkey

      Inline [

      userid, acctid, orDate, orname

      opp1u, opp9a, '1/30/2018', name1

      opp2u, opp8a, '1/31/2018', name2

      opp3u, opp7a, '2/1/2018', name3

      opp4u, opp6a, '2/2/2018', name4

      ];

       

       

      User:

      Load *, AutoNumberHash128(userid, acctid) as %Userkey 

      Inline [

      userid, acctid, Div

      opp1u, opp9a, A1

      opp2u, opp8a, A2

      opp3u, rrr,    A3

      CSMP1u, CSMP9a, B1

      CSMP2u, CSMP8a, B2

      CSMP3u, fff, B3

      ];

       

       

      calendar:

      Load * Inline [

      calendardate, Quarter

      '1/30/2018', Q1

      '1/31/2018', Q1

      '2/1/2018', Q2

      '2/2/2018', Q2

      '3/1/2018', Q3

      '3/2/2018', Q3

      ];

       

       

      Login:

      Load *,  AutoNumberHash128(userid, logDate) as %Loginkey

      Inline[

      userid, logDate, empname

      CSMP1u, '1/30/2018', CSMP1uname

      CSMP2u, '1/31/2018', CSMP2uname

      CSMP1u, '3/1/2018', CSMP1uname

      CSMP2u, '3/2/2018', CSMP2uname

      opp1u, '1/30/2018', opp1uname

      opp2u, '1/31/2018', opp2uname

      ];

       

       

      Link_table:

      load %Orrkey,

           userid,

           acctid,

      orDate as calendardate

      resident ORR;

      drop fields  userid, acctid, orDate from ORR;

       

       

      Concatenate(Link_table)

      load %Loginkey,

      userid, // don't have acctid field here

      logDate as calendardate

      resident Login;

       

       

      drop fields userid, logDate from Login;

       

       

      left join (Link_table)

      load userid,

      acctid,

      %Userkey

      resident User;

       

       

      drop fields userid, acctid from  User;

      exit script

      issue is datamodel looks good but not having %Userkey values for %Loginkey so that's the reason I could't able to see Div Values for %LoginKey.

      Calendar data associations working fine.

      appreciate for your help to get all the data in sample report.