Dynamic Column Names in Load Script

[#1]

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

[petter · Accepted solution]

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

[#3]

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.

[#4]

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

[#5]

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?

[petter]

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

[#7]

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