Flat Table Creation

[Mahajan]

Hi,

I need to create a flat table which includes ID, Name, Status, Price based on the following conditions : 

• ID from Master sheet should look up ID1 first and then ID2, and then Name from Master sheet should look up Name in Price sheet to find the exact matches between Master sheet and Price sheet and display the Price
• If ID in Master sheet did not match with ID1, ID2 and if Name from Master sheet did not match with Name in Price sheet then display the respective Dummy Prices under Price column
• If the status is ‘Imported’ then Price should be Not Available.

                                                Price Table                                                                                                          

                                                         Master Table

                                                Output

[ManimekalaiS]

First include below lines in Data Load Editor then in Sheet Drag the Table and add dimensions  C_ID,C_Name,C_Status,C_Price

//** Master Details
[Master]:
LOAD
ID,
Name as "M_Name",
Status,
"Dummy Price"
FROM [lib://SB2ApplyMap/Data source_Task2.xlsx]
(ooxml, embedded labels, table is Master);

//** Mapping ID Details
[Master_ID]:
Mapping
LOAD
ID as "M_ID",
ID
FROM [lib://SB2ApplyMap/Data source_Task2.xlsx]
(ooxml, embedded labels, table is Master);

//** Mapping Name Details
[Master_Name]:
Mapping
LOAD
Name as "M_Name",
Name
FROM [lib://SB2ApplyMap/Data source_Task2.xlsx]
(ooxml, embedded labels, table is Master);

//** Applymap for ID,Name with Price Details
[Price]:
LOAD
// ID1,
// ID2,
// Name as "P_Name",
// Price,
ApplyMap('Master_ID',ID1, ApplyMap('Master_ID',ID2,'N/A')) as "ID",
ApplyMap('Master_Name',Name,'N/A') as "Name",
"Price"
FROM [lib://SB2ApplyMap/Data source_Task2.xlsx]
(ooxml, embedded labels, table is Price)
;

//** Separate Master ID matched Details
NoConcatenate
[FilIDTable]:
LOAD Distinct
ID as "F_ID",
IF("ID" = 'N/A' and "Name" = 'N/A','Dummy Price',"Price") AS "F_Price"
Resident Price;
Left Join(FilIDTable)
LOAD Distinct
ID as "F_ID",
"M_Name" as "F_Name",
"Status" AS "F_Status",
"Dummy Price" AS "F_D_Price"
Resident Master;

///** Separate Master Name matched Details
NoConcatenate
[FilNameTable]:
LOAD Distinct
Name as "FN_Name",
IF("ID" = 'N/A' and "Name" = 'N/A','Dummy Price',"Price") AS "FN_Price"
Resident Price;
Left Join(FilNameTable)
LOAD Distinct
"M_Name" as "FN_Name",
ID as "FN_ID",
"Status" AS "FN_Status",
"Dummy Price" AS "FN_D_Price"
Resident Master;

//** Combine Separated Table and ger Final Result
NoConcatenate
[FinalTable]:
LOAD "F_ID" as "Fl_ID",
"F_Name" as "Fl_Name",
"F_Status" as "Fl_Staus",
"F_Price" as "Fl_Price",
"F_D_Price" as "Fl_D_Price"
Resident FilIDTable WHERE "F_ID" <> 'N/A';
OUTER join(FinalTable)
LOAD "FN_ID" as "Fl_ID",
"FN_Name" as "Fl_Name",
"FN_Status" as "Fl_Staus",
"FN_Price" as "Fl_Price",
"FN_D_Price" as "Fl_D_Price"
Resident FilNameTable WHERE "FN_Name" <> 'N/A';

//** From Final Result include if Status as Imported Price as Not Available
[ConCatTable]:
LOAD DISTINCT "Fl_ID" as "C_ID",
"Fl_Name" as "C_Name",
"Fl_Staus" as "C_Status",
IF("Fl_Staus" = 'Imported','Not Available',"Fl_Price") as "C_Price",
"Fl_D_Price" as "C_D_Price" Resident FinalTable;