Transform table

[#1]

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.

[rwunderlich]

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

[#3]

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

[rlp]

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

     EC

RESIDENT <the_generic_table>

)

You can then add the complementary fields with a *

LEFT JOIN <your_fact_table>

LOAD DISTINCT

     EC

RESIDENT <the_generic_table>

[#5]

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

[rwunderlich · Accepted solution]

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

[rlp]

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...

[#8]

Many thanks!