Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
i hope i'm in the right Forum.
I have a problem with two tables which contains dynamic columns in just one field.
The table (Columns.csv) has the column headers for each value in the field "Columns".
The table (Values.csv) has the values for each column in the field "Values".
These two tables are connected by the "Columns_ID" field.
The fields "Columns" and "Values" Data are separated by a blank.
My problem:
For each column in the field "Columns" from (Columns.csv) I'd like to have the data from the field "Values" (from Values.csv).
I found a lot of possible ways, all being extremely slow, because the original tables in our OracleDB have million of rows.
Thanks a lot for your answers!
Could you let us know which approaches you have already rejected as being too slow so I don't propose those same solutions again?
-Rob
Don't know if this will be too slow for you, but here's the approach I would try:
NameMap:
MAPPING
LOAD COLUMN_ID,
COLUMNS
FROM
Columns.xlsx
(ooxml, embedded labels, table is columns);
Data:
Generic
LOAD
DATA_NUMBER,
ApplyMap('NameMap', COLUMN_ID),
VALUES
FROM
Values.xlsx
(ooxml, embedded labels, table is Values);
DataFinal:
LOAD 1 as dummyField AutoGenerate 0;
FOR i = NoOfTables()-1 to 0 step -1
LET vTable = TableName($(i));
IF '$(vTable)' LIKE 'Data.*' THEN
JOIN (DataFinal) LOAD * Resident [$(vTable)];
DROP TABLE [$(vTable)];
ENDIF
NEXT i
DROP FIELD dummyField;
-Rob
My solution for now was to join these two tables and split the data of the field COLUMNS to Column1, Column2... by an view in our OracleDB.
So i had Column1 which only contains the data of the first Column (as Example: "Energy-sum-kWh")
In Qlikview i did that Script for each Column. This script will split the data of the field VALUES and 'join' it to the correct Column-data.
As Example:
Energy-sum-kWh
103348
380490
let zFieldCount = FieldValueCount('Column1');
for z = 1 to $(zFieldCount)
let zFieldName = FieldValue('Column1',$(z));
DATA: load
DATA_NUMBER,FROM, TO,
subfield(VALUES,' ') as [$(zFieldName)]
resident VIEW_FROM_ORACLE_DB
where Column1 = '$(zFieldName)';
Concatenate (Data)
LOAD *; next z
Because of the needed view in the Oracle Database and possible 12 Column-datas in COLUMNS it did take a lot of time and i'm sure not the best solution.
Thanks for your Script, but it won't do what i want:
I'd like to split the Columns and the Values in the fields.
So the COLUMN and VALUES are delimted by spaces? Same idea, it's the GENERIC keyword I'm suggesting rather than looping through the data. How about:
Raw:
LOAD
Distinct
COLUMN_ID,
COLUMNS
FROM
Columns.xlsx
(ooxml, embedded labels, table is columns);
JOIN (Raw)
LOAD
DATA_NUMBER,
FROM,
TO,
VALUES,
COLUMN_ID
FROM
Values.xlsx
(ooxml, embedded labels, table is Values);
Data:
Generic
LOAD
DATA_NUMBER,
SubField(COLUMNS, ' '),
SubField(VALUES, ' ')
Resident Raw;
-Rob
Hello Rob,
thanks, it works perfect! It took about 15 min to do this job with 1,6 million rows in the table Values.
Can you give an idea, to concatenate the created "Data.XXX" Tables in just one table without naming each table?
And is it possible to remove an unwanted Columns and Values Data? (to speed it up?)
As example: I don't want the data of "Costs-Net-€"
You can reassemble the Data.* tables using the same loop I posted in my first example, using CONCATENATE instead of JOIN.
To exclude certain columns, you'll have to subfield() the columns and values into a temp table and then filter that table as input to the Generic Load. See attached.
-Rob
Hello Rob,
if there is a relation between column and field values based on their subfield positions then maybe your generic load could be changed e.g. like
Data:
Generic
LOAD DATA_NUMBER,
SubField(COLUMNS, ' ',IterNo()),
SubField(VALUES, ' ',IterNo())
Resident Raw
While IterNo()<=RangeMax(SubStringCount(COLUMNS,' '),SubStringCount(VALUES,' '))+1;
to keep this relation. I guess otherwise the double 2-parameter subfield() functions would create a cartesian product.
regards
Marco
Good point Marco. I hadn't noticed the Cartesian product.
-Rob