Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all! I have a csv file that I want to transpose in the load script. I know there is a wizard for this, but doesn't seem to do it correctly. My data looks like this (simplified):
Record | Field | Value
1 | Firstname | Harrison
1 | Lastname | Ford
2 | Firstname | Will
2 | Lastname | Smith
I want it to be loaded like this
Record | Firstname | Lastname
1 | Harrison | Ford
2 | Will | Smith
Any suggestions?
@Maverick974 One solution :
Input:
LOAD * INLINE [
Record,Field,Value
1,Firstname,Harrison
1,Lastname,Ford
2,Firstname,Will
2,Lastname,Smith
];
CombinedGenericTable:
Load distinct Record resident Input;
DATA:
Generic LOAD Record,Field,Value resident Input;
drop table Input;
FOR i = NoOfTables()-1 to 0 STEP -1
LET vTable=TableName($(i));
IF WildMatch('$(vTable)', 'DATA.*') THEN
LEFT JOIN ([CombinedGenericTable]) LOAD distinct * RESIDENT [$(vTable)];
DROP TABLE [$(vTable)];
ENDIF
NEXT i
output:
@Maverick974 One solution :
Input:
LOAD * INLINE [
Record,Field,Value
1,Firstname,Harrison
1,Lastname,Ford
2,Firstname,Will
2,Lastname,Smith
];
CombinedGenericTable:
Load distinct Record resident Input;
DATA:
Generic LOAD Record,Field,Value resident Input;
drop table Input;
FOR i = NoOfTables()-1 to 0 STEP -1
LET vTable=TableName($(i));
IF WildMatch('$(vTable)', 'DATA.*') THEN
LEFT JOIN ([CombinedGenericTable]) LOAD distinct * RESIDENT [$(vTable)];
DROP TABLE [$(vTable)];
ENDIF
NEXT i
output:
Amazing, Taoufiq! Worked like a charm! Thanks a lot!
Hi Taoufiq,
How can I store the new output in a qvd file?
Thank you in advance!