5 Replies Latest reply: Jan 10, 2017 10:55 AM by David Ambrozie RSS

    Create script expression from variable no of fields

    Michiel van de Goor

      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