How convert table from rows to columns

[Anonymous]

Hi ,

how can i trasform a table like this :

items    stores        quantity

    A         1             5

    A         10           1

    B         1             2

    C         10           3

in a table like This?

    items    Quantity stores 1    Quantity stores 10   

    A             5                                1

    B             2                                0

    C             0                                3

For the moment data base have onli stores 1 and 10

Tank for help

[IAMDV]

Hi Miguel,

I hope you are doing well. I have been following this thread and I have learnt something new today, i.e. Generic Load. Thank you very much for sharing .

However, I wanted to know, how does QV identify which column values to transpose as additional Columns. I mean in this example we have Items, Stores and Quantity. So how does QV knows that we wanted to use Stores Column values to transpose as further columns? And what happens if I have one more column before the Items, for example if I have a region column before Items then QV is creating synthetic keys. How do I solve this issue?

Thanks in advance.

Cheers - DV

[Miguel_Angel_Baeyens]

Hi D V and thanks for your support as usual, it's really appreciated,

Similar to what QlikView does by default in the Crosstable load (cols to rows, roughly speaking), being Generic load the opposite (cols to rows), QlikView always uses the first column as the one to link the associated value of all the other columns in the table. To be more precise, QlikView understands that the first column stores the name of the fields, and the following fields store the attributes or values corresponding to the first one. So the link/key field must be always loaded in the first place regardless how it comes from the source (SQL, RESIDENT, etc...).

The resulting tables cannot be joined or concatenated without a loop (I'm thinking of a FOR running the NoOfTables() function and concatenating or joining them). You cannot set that you have more than one column for the field names (as you do can in the Crosstable with the third parameter).

So if you want to use stores instead of items, you should load them first. And if you have additional fields before items you'd like to keep, you will have to do a double load step from the same source, one "normal" load with two fields (region and items according to your example) and the other with generic load (items, stores and quantity).

Hope that makes sense and helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

[IAMDV]

Miguel - Very useful. This is very clear now. Thank you again for taking time to explain. I really appreciate your effort to give detailed answers.

Many thanks - DV