topic Re: Converting a table with dynamic columns into static columns in QlikView

Converting a table with dynamic columns into static columns

Mon, 31 Jul 2017 13:58:46 GMT

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!

Re: Converting a table with dynamic columns into static columns

rwunderlich — Mon, 31 Jul 2017 17:28:30 GMT

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

Re: Converting a table with dynamic columns into static columns

rwunderlich — Mon, 31 Jul 2017 17:51:51 GMT

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

Re: Converting a table with dynamic columns into static columns

Tue, 01 Aug 2017 07:21:41 GMT

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.

Re: Converting a table with dynamic columns into static columns

Tue, 01 Aug 2017 07:25:04 GMT

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.

Re: Converting a table with dynamic columns into static columns

rwunderlich — Tue, 01 Aug 2017 16:10:49 GMT

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

Re: Converting a table with dynamic columns into static columns

Thu, 03 Aug 2017 10:23:18 GMT

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

Re: Converting a table with dynamic columns into static columns

rwunderlich — Thu, 03 Aug 2017 17:38:37 GMT

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

MarcoWedel — Fri, 04 Aug 2017 00:16:48 GMT

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

Re: Converting a table with dynamic columns into static columns

rwunderlich — Fri, 04 Aug 2017 15:13:09 GMT

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

-Rob