Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
Not applicable

Dynamic Column Names in Load Script

Hi struggling with following problem:

let's say i have Data like:

1-slug1-slug2

2-slug1-slug3

e.g:

1-gender-age

2-country-city

e.g:

1-male-20

2-ger-muc

So, first Step was to split the string into separate Columns.

I did this with SubString()

So Result is Table:

Identifierslug1slug2
1male20
2germuc

What I am trying to achieve now:

Based on the Identifier the slugs have a different Meaning with different Data.

So what I´m trying to achieve now, is to Create New Columns and name them based on the Identifier

For the sample data it should look like:

Identifiergenderagecountrycity
1male20
2germuc

Thank you for any help

1 Solution

Accepted Solutions
Highlighted
MVP
MVP

Re: Dynamic Column Names in Load Script

I think your need could be satisfied with the GENERIC LOAD.

Have a look at this document by Henric Cronström: The Generic Load

And you could also read Rob Wunderlich's blog:

Use cases for Generic Load | Qlikview Cookbook

View solution in original post

6 Replies
Highlighted
MVP
MVP

Re: Dynamic Column Names in Load Script

I think your need could be satisfied with the GENERIC LOAD.

Have a look at this document by Henric Cronström: The Generic Load

And you could also read Rob Wunderlich's blog:

Use cases for Generic Load | Qlikview Cookbook

View solution in original post

Highlighted
Not applicable

Re: Dynamic Column Names in Load Script

You can use GENERIC function or For Loop to create Dynamic Field names.

Do you have 3 input tables ? I don't understand which tables are source tables and what is the final desired out put. Can you please post the sample source data with expected output.

Highlighted
Not applicable

Re: Dynamic Column Names in Load Script

my raw data is looking like that:

FACT TABLE:

idslug
11-male-35
22-ger-muc

I also have a Table with the meaning of the several parameters in the slug

INPUT TABLE:

identifierslug1slug2
1genderage
2countrycity

depending on the identifier in the slug the parameters have different meanings.

e.g:

if identifier = 1: the middle part of the slug is gender

if identifier = 2: the middle part of the slug is country

The input table should be extendable with new identifiers and meanings of the different parameters in the slug. The data is an excel file, where I can add new rows with new identifers etc.

in the next step i generated new columns with splitting the slug into the separate parts. I used the function "substring"

So now i have my FACT TABLE with following structure:

ididentifierslug1slug2
11male35
22germuc

But what i*m trying to achieve is a Result Table with all possible columns depending on the input table.

So Result should then look like this:

ididentifiergenderagecountrycity
11male35--
22--germuc

Hope it makes it clear now.

Thank you for any help

Highlighted
Not applicable

Re: Dynamic Column Names in Load Script

Hey guys.

I solved it with your suggestions of GENERIC Load

LET vColumnNo = NoOfFields('InputTable')-1;

FOR j = 1 to $(vColumnNo)

Flags:

Generic LOAD

id,

identifier,   

//Attribute & Value

[$(j)], slug$(j)

Resident FactTable;

NEXT j

But now i have one last problem.

When I´m doing the Gerneric Load in the FOR Loop, I get the error  “Blank field name not allowed”, if the field "value" contains blank values.

How can I avoid this error in LOAD?

Highlighted
MVP
MVP

Re: Dynamic Column Names in Load Script

Wouldn't an IF before Flags: which checks whether slug$(j) <> '' do the trick?

Highlighted
Not applicable

Re: Dynamic Column Names in Load Script

unfortunately not. I think i need some interrecord function to solve this. Because i have to check if the field has any value inside