Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Michiel_QV_Fan
Specialist
Specialist

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

1 Solution

Accepted Solutions
daveamz
Partner - Creator III
Partner - Creator III

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
Creator II
Creator II

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
Specialist
Specialist
Author

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
Creator II
Creator II

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
Partner - Creator III
Partner - Creator III

Hi Michiel,

Have a look at the script in the attached file.

I hope it will suit to your data set.

Best,

David