topic How convert table from rows to columns in QlikView

How convert table from rows to columns

Anonymous — Fri, 23 Sep 2011 09:47:13 GMT

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

How convert table from rows to columns

swuehl — Fri, 23 Sep 2011 09:57:29 GMT

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

How convert table from rows to columns

Anonymous — Fri, 23 Sep 2011 10:05:12 GMT

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

Re: How convert table from rows to columns

Miguel_Angel_Baeyens — Fri, 23 Sep 2011 10:15:08 GMT

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

How convert table from rows to columns

Anonymous — Fri, 23 Sep 2011 10:25:03 GMT

Yes Miguel

is that wath i was looking for.

Tank you very very much

Andrea

How convert table from rows to columns

Anonymous — Fri, 23 Sep 2011 11:26:12 GMT

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

How convert table from rows to columns

Miguel_Angel_Baeyens — Fri, 23 Sep 2011 11:31:57 GMT

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

How convert table from rows to columns

Anonymous — Fri, 23 Sep 2011 11:37:20 GMT

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

Re: How convert table from rows to columns

Miguel_Angel_Baeyens — Fri, 23 Sep 2011 12:04:08 GMT

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

How convert table from rows to columns

Anonymous — Fri, 23 Sep 2011 13:02:43 GMT

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

Re: How convert table from rows to columns

IAMDV — Fri, 23 Sep 2011 14:03:01 GMT

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

Re: How convert table from rows to columns

Miguel_Angel_Baeyens — Fri, 23 Sep 2011 17:31:06 GMT

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

Re: How convert table from rows to columns

IAMDV — Mon, 26 Sep 2011 15:49:07 GMT

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