Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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;
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