Data transposition

[#1]

Dear all,

I have a question how to transpose straith table to crosstable in QlikView.

I know that I can load crosstable and convert it into single table - but what if I have a source file in straight table and want to create crosstable?

For example my source file look like this:

Lp     A          B          C          D

1     0001     AAA     GG00     24

2     0001     AAA     GG01     36

3     0001     AAA     GG03     48  

4     0002     BBB     GG00     12    

5     0003     CCC     GG00     24

6     0003     CCC     GG02     32

7     0004     DDD     GG00     48

8     0004     DDD     GG01     72

9     0004     DDD     GG02     72

10   0004     DDD     GG03     72

and I want to create table like this:

Lp     A          B     GG00     GG01     GG02     GG03

1     0001     AAA     24          36           -            48

2     0002     BBB     12          -              -            - 

3     0003     CCC     24           -            32          -

4     0004     DDD     48          72          72          72

Will be gratefull for Your help.

[swuehl]

You should be able to do what you want using GENERIC LOADs:

The Generic Load

[swuehl]

Here is an example script:

GenericDB:

LOAD * INLINE [

Lp,     A   ,       B,          C,          D

1,     0001,     AAA,     GG00,     24

2,     0001,     AAA,     GG01,     36

3,     0001,     AAA,     GG03,     48 

4,     0002,     BBB,     GG00,     12   

5,     0003,     CCC,     GG00,     24

6,     0003,     CCC,     GG02,     32

7,     0004,     DDD,     GG00,     48

8,     0004,     DDD,     GG01,     72

9,     0004,     DDD,     GG02,     72

10,   0004 ,    DDD ,    GG03 ,    72

];

GenericLabel:

Generic LOAD Autonumber(A&B) as Lp, C,D Resident GenericDB;

  Set vListOfTables = ;

   For vTableNo = 0 to NoOfTables()

      Let vTableName = TableName($(vTableNo)) ;

      If Subfield(vTableName,'.',1)='GenericLabel' Then

         Let vListOfTables = vListOfTables & If(Len(vListOfTables)>0,',') & Chr(39) & vTableName & Chr(39) ;

      End If

   Next vTableNo

   CombinedGenericTable:

   Load distinct Autonumber(A&B) as Lp, A, B, A&B Resident GenericDB;

   For each vTableName in $(vListOfTables)

      Left Join (CombinedGenericTable) Load * Resident [$(vTableName)];

      Drop Table [$(vTableName)];

   Next vTableName

DROP TABLE GenericDB;

[#4]

Your scrips works good.

It's exacly what I needed, I will let You know when I will implement it into my app.

Thank You very much.