Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
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

];