topic Re: Crosstable or not ? in QlikView

Crosstable or not ?

rulohx87 — Fri, 11 Sep 2015 05:56:48 GMT

Hi,

I have a table with 20 fields. I want to convert these fields to dimension. I thought of a crosstable. But the table contains millions of records and not think it's the best option.

CLAVECCT	BAB	BAE	BCD	BCE	BCM	BCP	BDE	BDF	BDM	BEE	BGR	BIA	BIP	BM	BMR	BPC	BPF	BT	BTE	BTM	BUC
11DK867HJ20	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1
11DK867HJ20	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1

I'm thinking;

IF (GetFieldSelections (Catalog) = $ Field, Count (GetFieldSelection (catalog)))

May be..

IF (CATALOG = 'BAB', COUNT ('BAB'), COUNT ('BAB')) <----- text object (all columns)

...

Any tips?

Re: Crosstable or not ?

MK_QSL — Fri, 11 Sep 2015 09:02:50 GMT

CrossTable(Data,Value)

Load * From TableName;

Re: Crosstable or not ?

rulohx87 — Fri, 11 Sep 2015 14:38:07 GMT

I try a crosstab. But from a table of 4 million; the result of crossing is a table of 50 million and memory overflows.

Re: Crosstable or not ?

jonathandienst — Fri, 11 Sep 2015 14:48:35 GMT

Cross table is the correct way to go. If you cannot increase your server or PC memory to be large enough to handle the full data set, you may need to reduce the number of rows by limiting some range (like dates) or pre-aggregating the data in the source database.

The cross table has more rows but fewer fields and is usually not much larger than the original data. So without crosstabling you will probably run out of memory anyway - perhaps not during the load but almost certainly when working with the model.

Re: Crosstable or not ?

jonas_rezende — Fri, 11 Sep 2015 14:51:28 GMT

Hi, Raul Saucedo.

Try utilize summarized table, after of made crosstable. Example:

1. CrossTable (Data,Value)

Load * from Table;

Store [Table] into [..\Table.qvd] (qvd);

Drop table Table;

2. Load

CLAVECCT,

Count(CATALOG) as CATALOG

From Table.qvd (qvd)

Group by

CLAVECCT;

Item 2 will reduce the data.

I hope this helps!