Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Team,
Please see the below Sample source and Expected Output
The Values under Column Value should be taken as New Field as shown below.
Source Table | |||||||||||
Template | Request Date | PAC | PROJECT | ASSET | Row Number | Column Number | Column Type | Master Table | Column Value | ||
BOX1 | 8/10/2017 | Shell | PRJ1 | ASSET1 | 1 | 1 | TEXT | Topside | |||
BOX1 | 8/10/2017 | Shell | PRJ1 | ASSET1 | 1 | 2 | TEXT | Feed | |||
BOX1 | 8/10/2017 | Shell | PRJ1 | ASSET1 | 1 | 3 | TEXT | null | |||
BOX1 | 8/10/2017 | Shell | PRJ1 | ASSET1 | 1 | 4 | TEXT | DESC 123 | |||
BOX1 | 8/10/2017 | Shell | PRJ1 | ASSET1 | 1 | 5 | Integer | 11 | |||
BOX1 | 8/10/2017 | Shell | PRJ1 | ASSET1 | 1 | 6 | Integer | 1 | |||
BOX1 | 8/10/2017 | Shell | PRJ1 | ASSET1 | 1 | 7 | Decimal | 1244 | |||
Expected Output | |||||||||||
Template | Request Date | PAC | PROJECT | ASSET | Facility | Item Type | Sub Item Type | Desc | No of Units | Units | CostPerUnit |
BOX1 | 8/10/2017 | Shell | PRJ1 | ASSET1 | Topside | Feed | null | DESC 123 | 11 | 1 | 1244 |
Hi Senthil,
How do you identify the field name for the Column values? Is that static One (Always 7)
If it is static, You can just read one by one column number with the filter and do the left join.
if it is a dynamic, may be you can try with Generic Load.
Check this blog : The Generic Load
Directory;
T1:
Generic LOAD emplate,
[Request Date],
PAC,
PROJECT,
ASSET,
'Field'&[Column Number] as Rec,
[Column Value]
FROM
temp_comm\Book1.xlsx
(ooxml, embedded labels, table is Sheet1);
Combined:
Load distinct emplate FROM
temp_comm\Book1.xlsx
(ooxml, embedded labels, table is Sheet1);
FOR i = NoOfTables()-1 to 0 STEP -1
LET vTable=TableName($(i));
IF WildMatch('$(vTable)', 'T1.*') THEN
LEFT JOIN ([Combined]) LOAD * RESIDENT [$(vTable)];
DROP TABLE [$(vTable)];
ENDIF
NEXT i
//Field Name Changes
NoConcatenate
Summary:
LOAD emplate,
[Request Date],
PAC,
PROJECT,
ASSET,
Field1 as Facility,
Field2 as [Item Type],
Field3 as [Sub Item Type],
Field4 as [Desc],
Field5 as [No. of Units],
Field6 as [Units],
Field7 as [CostPerUnit]
Resident Combined;
DROP Table Combined;
OutPut: