Help me with a table transformation

[shekhar_analyti]

Hi All ,

Help me with a table transformation :

LOAD * INLINE [

Machine_Line_Code, Year, Source, BEND, Zone, SCORE, OUT0F, FIELD1, FIELD2, FIELD3, FIELD4, FIELD5, FIELD6, FIELD7, FIELD8
1001BMR, 2017, JOY INC, N, Z1, 5, 10, , , , , , , ,
1002BMR, 2018, MINES , Y, Z2, 60, 100, , , , , , , ,
X-F002FFC, 2019, MOLASSES CORP, N, Z3, 3.5, 5, , , , , , , ,
Y-F003FFC, 2020, BREEW, , Z4, 24, 50, , , , , , , ,
];

Thanks & Regards

[agni_gold · Accepted solution]

PFB script or attached file for required info.

inputtab:
Load RecNo() as ID,Machine_Line_Code, Year, Source, BEND,SCORE, OUT0F, FIELD1, FIELD2, FIELD3, FIELD4, FIELD5, FIELD6, FIELD7,Zone, FIELD8;
LOAD * INLINE [
Machine_Line_Code, Year, Source, BEND, Zone, SCORE, OUT0F, FIELD1, FIELD2, FIELD3, FIELD4, FIELD5, FIELD6, FIELD7, FIELD8
1001BMR, 2017, JOY INC, N, Z1, 5, 10, , , , , , , ,
1002BMR, 2018, MINES , Y, Z2, 60, 100, , , , , , , ,
X-F002FFC, 2019, MOLASSES CORP, N, Z3, 3.5, 5, , , , , , , ,
Y-F003FFC, 2020, BREEW, , Z4, 24, 50, , , , , , , ,
];

gentab:
Generic Load ID,Machine_Line_Code, Year, Source, BEND,OUT0F, FIELD1, FIELD2, FIELD3, FIELD4, FIELD5, FIELD6, FIELD7,FIELD8,Zone,SCORE Resident inputtab;

resulttab:
LOAD Distinct ID,Machine_Line_Code, Year, Source, BEND,OUT0F, FIELD1, FIELD2, FIELD3, FIELD4, FIELD5, FIELD6, FIELD7,FIELD8 Resident inputtab;
FOR i=0 to NoOfTables()
tablelist:
LOAD TableName($(i)) as tablename AutoGenerate 1
Where WildMatch(TableName($(i)),'gentab.*');
NEXT i
FOR i=1 to FieldValueCount('tablename')
LET Vtable=FieldValue('tablename',$(i));
Left Join (resulttab)
LOAD * Resident [$(Vtable)];
DROP Table [$(Vtable)];
NEXT i
DROP Tables tablelist,inputtab;

[sunny_talwar · Accepted solution]

You can also try this

Table:
LOAD *,
	 Hash128(Machine_Line_Code&'|'&Year&'|'&Source&'|'&BEND&'|'&OUT0F&'|'&FIELD1&'|'&FIELD2&'|'&FIELD3&'|'&FIELD4&'|'&FIELD5&'|'&FIELD6&'|'&FIELD7&'|'&FIELD8) as Key;
LOAD * INLINE [
    Machine_Line_Code, Year, Source, BEND, Zone, SCORE, OUT0F, FIELD1, FIELD2, FIELD3, FIELD4, FIELD5, FIELD6, FIELD7, FIELD8
    1001BMR, 2017, JOY INC, N, Z1, 5, 10, , , , , , , ,
    1002BMR, 2018, MINES, Y, Z2, 60, 100, , , , , , , ,
    X-F002FFC, 2019, MOLASSES CORP, N, Z3, 3.5, 5, , , , , , , ,
    Y-F003FFC, 2020, BREEW, , Z4, 24, 50, , , , , , , ,
];

FinalTable:
LOAD DISTINCT Key,
	 Machine_Line_Code,
	 Year,
	 Source,
	 BEND,
	 OUT0F,
	 FIELD1,
	 FIELD2,
	 FIELD3,
	 FIELD4,
	 FIELD5,
	 FIELD6,
	 FIELD7,
	 FIELD8
Resident Table;

FOR i = 1 to FieldValueCount('Zone')

	LET vZone = FieldValue('Zone', $(i));
	TRACE $(vZone);
	
	Left Join (FinalTable)
	LOAD DISTINCT Key,
		 Machine_Line_Code,
		 Year,
		 Source,
		 BEND,
		 OUT0F,
		 FIELD1,
		 FIELD2,
		 FIELD3,
		 FIELD4,
		 FIELD5,
		 FIELD6,
		 FIELD7,
		 FIELD8,
		 SCORE as [$(vZone)]
	Resident Table
	Where Zone = '$(vZone)';

NEXT

DROP Table Table;