Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Highlighted
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
MVP & Luminary
MVP & Luminary

Re: Converting a table with dynamic columns into static columns

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

MVP & Luminary
MVP & Luminary

Re: Converting a table with dynamic columns into static columns

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

Re: Converting a table with dynamic columns into static columns

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

Re: Converting a table with dynamic columns into static columns

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.

MVP & Luminary
MVP & Luminary

Re: Converting a table with dynamic columns into static columns

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

Re: Converting a table with dynamic columns into static columns

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

MVP & Luminary
MVP & Luminary

Re: Converting a table with dynamic columns into static columns

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

Re: Converting a table with dynamic columns into static columns

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

MVP & Luminary
MVP & Luminary

Re: Converting a table with dynamic columns into static columns

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

-Rob