Transpose in load script

[Maverick974]

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?

[Taoufiq_Zarra · Accepted solution]

@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]

Amazing, Taoufiq! Worked like a charm! Thanks a lot!

[moath]

Hi Taoufiq,

How can I store the new output in a qvd file?

Thank you in advance!