topic Transpose in load script in QlikView

Transpose in load script

Maverick974 — Thu, 08 Oct 2020 10:24:42 GMT

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?

Re: Transpose in load script

Taoufiq_Zarra — Thu, 08 Oct 2020 10:42:23 GMT

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

Re: Transpose in load script

Maverick974 — Thu, 08 Oct 2020 12:34:52 GMT

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

Re: Transpose in load script

moath — Thu, 09 Jun 2022 12:21:36 GMT

Hi Taoufiq,

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

Thank you in advance!