Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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.
CrossTable(Data,Value)
Load * From TableName;
I try a crosstab. But from a table of 4 million; the result of crossing is a table of 50 million and memory overflows.
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.
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!