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