1 Reply Latest reply: Sep 26, 2015 5:21 AM by Marcus Sommer RSS

    [ASK] How to create a relation from 2 table with different Field&Value

    Rendi Yanuar

      Hello Qlik Master,

       

      I have 2 table, Master and Trans table.

      Master table have 2 field :

      1. ID (1,2,3)

      2. Name ('Alex', 'Diana', 'Lidya')

       

      Trans table have 3 field :

      1. Message ('Working in the field is Alex's job', 'His wife name is Diana','Lidya goes to market')

      2. Timestamp

      3. Status

       

       

      Based on above description, how to create a Name column in Trans table based on Name column in Master table?

       

       

      Thank you so much

        • Re: [ASK] How to create a relation from 2 table with different Field&Value
          Marcus Sommer

          I would try something like this:

           

          Master:

          Load chr(39) & '*' & concat(distinct Name, '*' & chr(39) & ',' & chr(39) & '*', ID) & '*' & chr(39) as NameList  Inline [

          ID, Name

          3, Alex

          5, Diana

          8, Lidya

          ];

           

          let vNameList = peek('NameList', -1, 'Master');

           

          MapTableToMaster:

          Mapping Load rowno() as RowNo, ID Inline [

          ID, Name

          3, Alex

          5, Diana

          8, Lidya

          ];

           

          Trans:

          Load *, applymap('MapTableToMaster', wildmatch(Message, $(vNameList)), 'n.n') as ID Inline [

          Message, Timestamp, Status

          Working in the field is Alex's job, 1, A

          His wife name is Diana, 2, A

          Lidya goes to market, 3, B

          ];