Merging Multiple Rows into Single Row as Input

[psenthilbtech]

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

[settu_periasamy]

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: