Create script expression from variable no of fields

[Michiel_QV_Fan]

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

[gardenierbi]

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?

[marcus_sommer]

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

[Michiel_QV_Fan]

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]

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

[daveamz · Accepted solution]

Hi Michiel,

Have a look at the script in the attached file.

I hope it will suit to your data set.

Best,

David