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

[swuehl]

Hi,

I think a pivot table chart should do:

use Items and strores as dimensions and =sum(quantity) as expression.

Drag the stores to the top of your table with the mouse.

You could use an expression like

='Quantity stores ' & stores

as label for dimension stores

Hope this helps,

Stefan

[Anonymous]

HI,

I need  solve my problem in the script , because new table i have to to join with other tables

and i don't know how to do..

Thank for help

Andrea

[Miguel_Angel_Baeyens · Accepted solution]

Hi Andrea,

Try to do a Generic load

GENERIC LOAD * INLINE [
items, stores, quantity
A, 1, 5
A, 10, 1
B, 1, 2
C, 10, 3
];

Is that what you are looking for?

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

[Anonymous]

Yes Miguel 

is that wath i was looking for.

Tank you very very much

Andrea

[Anonymous]

Little problem .....

i have used  the GENIRC LOAD on my real database ( just 30.000 rows) and the time of relod was of 25 min just

for that single database....

There is a way to have same result but using a different way in the script?

Thank

[Miguel_Angel_Baeyens]

Andrea,

If the number of stores is limited and small, you can do several loads (meaning QlikView, not SQL, but that will do too) using WHERE and then JOIN them using "item" as the key field.

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

[Anonymous]

Thank Miguel,

The stores are only 2  : nr 1 and nr 10 .... the problem is i don't have any idea how to to many loads with Join and so on...

thank for your help

[Miguel_Angel_Baeyens]

Andrea,

I'm thinking of something similar to the following:

OriginalData:
LOAD Chr(64 + Ceil(Rand() * 3)) AS ItemID,
           'Store' & Ceil(Rand() * 2) AS StoreID,
           Ceil(Rand() * 10) AS Quantity
AUTOGENERATE 15;
FromRowsToCols:
LOAD ItemID,
           StoreID AS Store1,
           Quantity 
RESIDENT OriginalData
WHERE StoreID = 'Store1';
CONCATENATE LOAD ItemID,
           StoreID AS Store2,
           Quantity 
RESIDENT OriginalData
WHERE StoreID = 'Store2';
DROP TABLE OriginalData;

Where "OriginalData" is your data source, table "FromRowsToCols" does a concatenation of the two possible sources.

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

[Anonymous]

Fantastic Miguel ....thank very much.

I made a modify on the CONCATENATE LOAD : the field Quantity i have renamed in Qyantity2

so  i have  quantity distinct for store  ... and that is wath i need

Thank very very much again

Andrea