How to sync 2 mapping files

[wendytham]

Dear All,

I have 2 excel files with raw data, one is model with group, and another one is model with category.

And I have 2 excel files with mapping group, one for model with group and another for model with category.

How to merge these 2 excel raw data file and map both group and category with Final_cat?

Model with Group:

Model	Group	Amt
TW1122	A	21
GD3322	B	87
HF1122	C	46
RE2211	D	31
WA1111	A	4
FD45451	B	123
TE12311	C	79
QW45644	D	20

Model  with Category:

Model	Category	Amt
TW1122	E	21
GD3322	F	87
HF1122	G	46
RE2211	H	31
WA1111	E	4
FD45451	F	123
TE12311	G	79
QW45644	H	20

Mapping:

Group	Final_Cat		Category	Final_Cat
A	A1		E	A1
B	B1		F	B1
C	C1		G	C1
D	D1		H	D1

[jpenuliar]

Try this:

group:
LOAD
Model,
"Group"
FROM [lib://AttachedFiles/Book1.xlsx]
(ooxml, embedded labels, table is Sheet1);

Join(group)
category:
LOAD
Model,
Category
FROM [lib://AttachedFiles/Book1.xlsx]
(ooxml, embedded labels, table is Sheet2);

NoConcatenate
Final_Cat:
Load Distinct
"Group",
Category
Resident group;

Drop Table group;

[wendytham]

Hi, thank you for your reply.

I would like to join the 2 tables with the variable "Final_Cat".

How to join these 2 tables and load only the below variables?

1) Model

2) Final_Cat

2) Amt