topic Re: Dynamic Column Names in Load Script in QlikView

Dynamic Column Names in Load Script

Fri, 15 Jan 2016 14:20:42 GMT

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:

Identifier	slug1	slug2
1	male	20
2	ger	muc

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:

Identifier	gender	age	country	city
1	male	20
2			ger	muc

Thank you for any help

Re: Dynamic Column Names in Load Script

petter — Sat, 16 Jan 2016 00:11:44 GMT

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

Re: Dynamic Column Names in Load Script

Sat, 16 Jan 2016 04:56:51 GMT

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.

Re: Dynamic Column Names in Load Script

Mon, 18 Jan 2016 08:54:40 GMT

my raw data is looking like that:

FACT TABLE:

id	slug
1	1-male-35
2	2-ger-muc

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

INPUT TABLE:

identifier	slug1	slug2
1	gender	age
2	country	city

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:

id	identifier	slug1	slug2
1	1	male	35
2	2	ger	muc

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:

id	identifier	gender	age	country	city
1	1	male	35	-	-
2	2	-	-	ger	muc

Hope it makes it clear now.

Thank you for any help

Re: Dynamic Column Names in Load Script

Mon, 18 Jan 2016 11:04:44 GMT

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?

Re: Dynamic Column Names in Load Script

petter — Mon, 18 Jan 2016 12:19:27 GMT

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

Re: Dynamic Column Names in Load Script

Mon, 18 Jan 2016 12:32:59 GMT

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