Skip to main content
Woohoo! Qlik Community has won “Best in Class Community” in the 2024 Khoros Kudos awards!
Announcements
Save $600 on Qlik Connect registration! Sign up by Dec. 6 to get an extra $100 off with code CYBERSAVE: REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
rendiyan
Partner - Creator
Partner - Creator

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

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

1 Reply
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

];