Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Highlighted
michielvandegoo
Valued Contributor

Create script expression from variable no of fields

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

Tags (2)
1 Solution

Accepted Solutions
daveamz01
Contributor III

Re: Create script expression from variable no of fields

Hi Michiel,

Have a look at the script in the attached file.

I hope it will suit to your data set.

Best,

David

View solution in original post

5 Replies
gardenierbi
Contributor

Re: Create script expression from variable no of fields

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?

MVP & Luminary
MVP & Luminary

Re: Create script expression from variable no of fields

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

michielvandegoo
Valued Contributor

Re: Create script expression from variable no of fields

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.

gardenierbi
Contributor

Re: Create script expression from variable no of fields

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

daveamz01
Contributor III

Re: Create script expression from variable no of fields

Hi Michiel,

Have a look at the script in the attached file.

I hope it will suit to your data set.

Best,

David

View solution in original post