topic Re: Create script expression from variable no of fields in QlikView

Create script expression from variable no of fields

Michiel_QV_Fan — Tue, 10 Jan 2017 14:50:07 GMT

I want to create this:

rangesum(Field1, Field2, Field3) as .....

My challenge is this:

I have to loop over multiple qvd's. Each qvd is processed one by one Each qvd has a different number of columns. The rangesum must be performed over a variety of 43 to 48 columns.

I was trying to generate a new empty table with only the headers to concatenate the data into that table.

The result of the empty table would be 7 fixed ID fields + the rangesum expression.

Like this:

Fact:

Load

'' as ID1,

'' as ID2

'' as ID3,

etc.,,

Rangesum(Field1, Field2, Field3) as .....

autogenerate(0);

Concatanate

Fact_data:

Load * from ..... .qvd (qvd);

I have tried to use iterno() in the script in variables to generate the fieldnames.

And I tried to concat multiple variables to 1 new variable with the rangesum added (with the purpose to add that to my Fact table.

However, this didn't give me the results.

Please advise.

Thanks

Re: Create script expression from variable no of fields

gardenierbi — Tue, 10 Jan 2017 15:01:13 GMT

Hi Michiel,

You can loop through your fields before loading them with QvdNoOfFields(). You'll get the fieldnames with the function QvdFieldName (). Create a variable before you load each QVD and use this variable within your Rangesum.

Will this works?

Re: Create script expression from variable no of fields

marcus_sommer — Tue, 10 Jan 2017 15:14:24 GMT

Could it be that you have stored crosstables within your qvd's? If yes it might be appropriate to change this into "normal" data-structures. How, see here: The Crosstable Load.

- Marcus

Re: Create script expression from variable no of fields

Michiel_QV_Fan — Tue, 10 Jan 2017 15:16:34 GMT

Sander, yes that is the basic idea.

However, I have not succeeded yet to create this:

Rangesum(Field1. Field2, ..) as ....

I hoped I could do this with concat, a variable and a loop but that generates only 1 field inside the concat, not 43 to 48 fields.

for a = 7 to $(number of fields in qvd)

let variable_name = concat($(qvd_fieldname)_$a), ',');

next a

I'm stuck in generating a comma separated list.

Re: Create script expression from variable no of fields

gardenierbi — Tue, 10 Jan 2017 15:34:04 GMT

Something like this :

let variable_name = 0;

for a = 7 to $(number of fields in qvd)

let vFieldName = QvdFieldName ([your qvd], a).

let variable_name = '$(variable_name), $(vFieldName);

next a

Re: Create script expression from variable no of fields

daveamz — Tue, 10 Jan 2017 15:55:01 GMT

Hi Michiel,

Have a look at the script in the attached file.

I hope it will suit to your data set.

Best,

David