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

rendiyan — Sat, 26 Sep 2015 03:41:29 GMT

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 — Sat, 26 Sep 2015 09:21:40 GMT

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

];