Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Converting a table with dynamic columns into static columns

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!

9 Replies
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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

http://masterssummit.com

http://qlikviewcookbook.com

Not applicable
Author

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.

Not applicable
Author

Thanks for your Script, but it won't do what i want:

Table.PNG

I'd like to split the Columns and the Values in the fields.

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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

http://masterssummit.com

http://qlikviewcookbook.com

Not applicable
Author

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-€"

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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

http://masterssummit.com

http://qlikviewcookbook.com

MarcoWedel

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

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

Good point Marco. I hadn't noticed the Cartesian product.

-Rob