common rows from two tables

[pala_jaga]

HI I have two table

table1:

S1	Date
11	12/01/2022
22	13/01/2022
33	14/01/2022
44	15/01/2022
55	16/01/2022

table2:

P1	P2	P3
11	one	jj;gg;kk
22	one	ll;mm;nn
33	one	oo;pp;hh

I want ouput like below

p1	p2	p3	date
11	one	jj;gg;kk	12/01/2022
22	one	ll;mm;nn	13/01/2022
33	one	oo;pp;hh	14/01/2022

[rubenmarin · Accepted solution]

Hi, that's a join between the two tables:

FinalTable:
LOAD P1, P2, P3
From ...  Table2Source

Left Join (FinalTable)
LOAD S1 as P1, Date
From ...  Table1Source

[Lakshminarayanan_J · Accepted solution]

@pala_jaga 

Rename the Table1  field S1 to P1 using alias name 

and inner join the table with Table2 

Script:

T1:
Load * inline [
P1, Date
11, 12/01/2022
22, 13/01/2022
33, 14/01/2022
44, 15/01/2022
55, 16/01/2022
];
Inner Join(T1)
Load * inline [
P1, P2, P3
11, one, jj;gg;kk
22, one, ll;mm;nn
33, one, oo;pp;hh
];

Result:

[pala_jaga]

Hi @Lakshminarayanan_J 

But I want to load T1 and T2 as separate tables 

after that i want output like above

[pala_jaga]

Hi @rubenmarin 

But I want to load T1 and T2 as separate tables 

after that i want output like above

[rubenmarin]

Hi, for that you don't need to do anything in script, just create a table in design with the needed fields as dimensions, change the labels to show as you want and in the dimension P1 uncheck the option to show null values.

[Lakshminarayanan_J]

@pala_jaga 

You can do it in 2 way if you want separately :

1.as @ Pala_Jaga mentioned you need to rename the Table 1 field S1 to P1 and load the data

2. You can use inner Keep function instead of inner join , I wont join the table you can view table 2 as separate table in datamodel

Script for Inner Keep:

T1:
Load * inline [
P1, Date
11, 12/01/2022
22, 13/01/2022
33, 14/01/2022
44, 15/01/2022
55, 16/01/2022
];
Inner Keep (T1)

Load * inline [
P1, P2, P3
11, one, jj;gg;kk
22, one, ll;mm;nn
33, one, oo;pp;hh
];

for option one comment or remove the Inner Keep script and load the data

Data model:

UI: