topic Re: Data transposition in QlikView

Data transposition

Tue, 17 Nov 2015 13:50:22 GMT

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.

Re: Data transposition

swuehl — Tue, 17 Nov 2015 17:12:19 GMT

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

The Generic Load

Re: Data transposition

swuehl — Tue, 17 Nov 2015 17:30:27 GMT

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;

Re: Data transposition

Thu, 19 Nov 2015 12:10:29 GMT

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.