topic Re: Transform table in QlikView

Transform table

Wed, 19 Sep 2012 11:26:50 GMT

I would like to transform my table in Green to get the table in Red.

I tried with a crosstable but didn't succeed. Anyone can help me ? Thank you.

Re: Transform table

Wed, 19 Sep 2012 11:41:43 GMT

Please, upload the file again. Don´t see the image.

Re: Transform table

Wed, 19 Sep 2012 11:51:51 GMT

I've uploaded the xlsx file, image doesn't upload. Thank you.

Re: Transform table

Wed, 19 Sep 2012 13:10:31 GMT

Can you send a script code?

A regard.

Re: Transform table

Wed, 19 Sep 2012 14:14:19 GMT

I've uploaded the qvw. Many thanks.

Re: Transform table

Wed, 19 Sep 2012 15:06:03 GMT

I send a probably solution?

A regard.

Re: Transform table

Wed, 19 Sep 2012 17:19:46 GMT

Hello,

No I would like to transform this table, without joining it... but thank you for your answer!

Regards,

Re: Transform table

Wed, 19 Sep 2012 18:34:39 GMT

Take a look.

Re: Transform table

Wed, 19 Sep 2012 18:44:57 GMT

Thank you, but I have to do that in script...

Re: Transform table

Peter_Cammaert — Wed, 19 Sep 2012 21:32:29 GMT

How about this one? I don't think any other solution - object or script - would be able to handle unpredictable KZID values in a simple manner.

Peter

Re: Transform table

rwunderlich — Wed, 19 Sep 2012 22:07:10 GMT

The Generic Load feature should handlle this nicely. Look it up in the Ref Guide. Also see:

http://qlikviewnotes.blogspot.com/2010/05/use-cases-for-generic-load.html

-Rob

Re: Transform table

Thu, 20 Sep 2012 06:51:56 GMT

Thank you Rob, it works, but with a simplified table:

The table original can be good transformed
But not the V2, and that's what I need to transform. If anyone as an idea, it would be nice.

Regards, IP

Re: Transform table

rlp — Thu, 20 Sep 2012 12:16:40 GMT

Firstly should load your primary key ( EC) and the different field ( KZID) with a generic load and then add the others fields with GENERIC LOAD.

The problem is that QV create as many tables as there are diffrent values of KZID. You should so use the code at the bottom of the link provided by Rob to create a single table ( Be careful to adapt the name of the non-generic_table) (Rmk: if you don't have a fact table, creta one with

LOAD DISTINCT

RESIDENT <the_generic_table>

)

You can then add the complementary fields with a *

LEFT JOIN <your_fact_table>

LOAD DISTINCT

RESIDENT <the_generic_table>

Re: Transform table

Mon, 24 Sep 2012 19:29:33 GMT

Hi Richard,

I've done the load distinct EC... and the LEFT JOIN, I still don't understand how to transform my table with more than 3 fiels. Here's my code:

original:
LOAD * Inline [EC, KZID, Wert
1000, FI4030, 40
1000, FI4027, 66
1000, FI4023, 55
];
temp1:
generic load * resident original;
result:
load distinct EC resident original;
FOR i = 0 to NoOfTables()
TableList:
LOAD TableName($(i)) as Tablename AUTOGENERATE 1
WHERE WildMatch(TableName($(i)), 'temp1.*');
NEXT i
FOR i = 1 to FieldValueCount('Tablename')
LET vTable = FieldValue('Tablename', $(i));
LEFT JOIN (result) LOAD * RESIDENT $(vTable);
DROP TABLE $(vTable);
NEXT i
drop table TableList;

Thank you very much for your help, I'm blocked. IP

Re: Transform table

rwunderlich — Tue, 25 Sep 2012 19:06:19 GMT

Just specify the 3 fields you want to transform in the generic.

generic load EC, KZID, Wert resident original_v2;

What you do from there depends on what you want to do with the Jahr & Quartal fields. Do you want a row for each EC/Jahr/Quartal value wth the KZID fields repeated?

-Rob

Re: Transform table

rlp — Tue, 02 Oct 2012 14:33:11 GMT

Let's suppose that your table 'original' contain both the generic fields EC,KZID, Wert ans some additional fields e.g. add_1 , add_2 depending only on your primary key EC.

You should then modify the genric load in order to specify that only the three first fields are involved, which gives:

temp1:

GENERIC LOAD

EC ,

KZID ,

Wert

RESIDENT original ;

After this, the code gluing the different generated tables is fine and you could then add the additional fields with:

LEFT JOIN( resultat )

LOAD DISTINCT

EC ,

Ad_1 ,

Ad_2

RESIDENT original ;

Sorry for the late, I was in holiday...

Re: Transform table

Tue, 02 Oct 2012 16:36:22 GMT

Many thanks!