topic How To transpose multi field data in QlikView

How To transpose multi field data

Thu, 09 Feb 2012 20:30:18 GMT

Hi,

I have a table with multiple addresses listed for a business. So I have a mailing address and physical address

Lets say the layout is:

BizId, PAddL1, PAddl2,Pcity,PState,PZip, MAddL1, MAddl2,Mcity,MState,MZip

How can I transpose this while loading so I can get this structure:

BizId, AddType, AddL1, Addl2,city,State,Zip

Thanks.

-Sachin

How To transpose multi field data

Thu, 09 Feb 2012 21:00:50 GMT

You can read your file twice like this

Load
BizId,
PAddL1 as Addl1,
PAddl2 as Addl2,
Pcity as City,
PState as State,
PZip as Zip,
'Physical' as AddType
from yourDataFile ;
Concatenate Load
BizId,
MAddL1 as Addl1,
MAddl2 as Addl2,
Mcity as City,
MState as State,
MZip as Zip,
'Mailing' as AddType
from yourDataFile ;

How To transpose multi field data

Thu, 09 Feb 2012 22:43:39 GMT

I have 3.5 millions records and several fields like this in each record. This works for now, but the load is pretty slow as I am having to parse the file 4 times, with as many as 15 concat statements.

Working fine for now. But wish there was a more elegant solution.

Thanks.

How To transpose multi field data

CELAMBARASAN — Fri, 10 Feb 2012 05:49:58 GMT

Hi,

Instead of loading multiple times from data file you can do like this by altering the way Jean-Jacques Jesua suggestion to.

TempTable:
Load
          BizId,
          PAddL1,
          PAddl2,
          Pcity,
          PState,
          PZip,
          MAddL1,
          MAddl2,
          Mcity,
          MState,
          MZip
from datafile;
MainTable:
Load
BizId,
PAddL1 as Addl1,
PAddl2 as Addl2,
Pcity as City,
PState as State,
PZip as Zip,
'Physical' as AddType
Resident
      TempTable;
Concatenate Load
BizId,
MAddL1 as Addl1,
MAddl2 as Addl2,
Mcity as City,
MState as State,
MZip as Zip,
'Mailing' as AddType
Resident
     TempTable;
Drop Table TempTable;

it reduces some more time.

Celambarasan

How To transpose multi field data

Fri, 10 Feb 2012 13:34:23 GMT

This seems a better approach!! Thanks.