Problems using variables in a LOAD INLINE

[#1]

I need to put variables in an inline load so I can save the results to a history file and I'm not getting the values of the variables in the end result.

Question: How can I get the value of the variable to show up in the inline load results? 

Variables:  I've been playing with both LET and SET statements.  Either would be acceptable.  I will need to put the set analysis in my final expressions but I made vTestSet a simple expression in case the complex one was the issue.

  LET vTestLet = REPLACE( '=sum({ <ProductGroupIMa = {|Antigens|}>} InvValPreviousMonth1)', '|', chr(39));

  SET vTestSet  = Sum(InvValPreviousMonth1);

The attribute "InvValPreviousMonth1" is from a database table load.

When I use the above variables in a straight table object, I get the expected values.

(expressions in the table are: =$(vTestLet) & =$(vTestSet))

When I use the variables in the load, I do not get anything useful.

Here is a listing of my load script.  The Value column is the one of interest, with the last column being an index so I can match the load line to the output line.

LOAD *  INLINE    // insert the monthly data into the table

  [

  PeriodType, ProductGroup, ValueType, Value, GlPeriod, FYear, CalendarMonth

  'M', 'TEST',   '', $(=$(vTestSet)), '', '', '13'

  'M', 'TEST',   '', =$(=$(vTestSet)),'', '', '14'

  'M', 'TEST',   '', =$vTestSet,      '', '', '15'

  'M', 'TEST',   '', =$(vTestSet),    '', '', '16'

  'M', 'TEST',   '', $(vTestSet),     '', '', '17'

  'M', 'TEST',   '', $(=vTestSet),    '', '', '18'

  'M', 'TEST',   '', {$(=vTestSet)},  '', '', '19'

  'M', 'TEST',   '', $(=$(vCurrentDate)),         '', '', '20'

  'M', 'TEST',   '', $(=$(vOpMgmtLastSavedYear)), '', '', '21'

  'M', 'TEST',   '', '$(vTestSet)',    '', '', '22'

  'M', 'TEST',   '', '=$(vTestSet)',   '', '', '23'

  'M', 'TEST',   '', $(=$(vTestLet)), '', '', '24'

  'M', 'TEST',   '', =$(=$(vTestLet)),'', '', '25'

  'M', 'TEST',   '', =$vTestLet,      '', '', '26'

  'M', 'TEST',   '', =$(vTestLet),    '', '', '27'

  'M', 'TEST',   '', $(vTestLet),     '', '', '28'

  'M', 'TEST',   '', $(=vTestLet),    $(vOpMgmtNewMonth), $(vOpMgmtNowYear), '29'

  'M', 'TEST',   '', {$(=vTestLet)},  $(vOpMgmtNewMonth), $(vOpMgmtNowYear), '30'

  'M', 'TEST',   '', '$(vTestLet)',   $(vOpMgmtNewMonth), $(vOpMgmtNowYear), '31'

  'M', 'TEST',   '', '=$(vTestLet)',  $(vOpMgmtNewMonth), $(vOpMgmtNowYear), '32' 

  ];

And this is the results of the above load. 

As you can see, I do not get any useful values shown.  (Line 21 just shows that non-complex variables do show up.)

Thanks,

John

[Gysbert_Wassenaar]

What you did is load text strings in an inline table. Those are text values and shown as such. If you want these strings to be treated as expressions then you need a more complex expression that turn the values of the Value field into evaluated expressions. See attached qvw for a simplified example.

[#3]

Quoting from the help files:  "The let statement ... evaluates the expression on the right side of the '=' before it is assigned to the variable. "

Based on the above statement, I had expected that vTestLet would contain the results of the evaluated expression ($495,858, in this case) and not the expression string itself.

Maybe my question should be how to I force the variable to contained the evaluated value instead of the expression string.

[Gysbert_Wassenaar]

LET vTestLet = REPLACE( '=sum({ <ProductGroupIMa = {|Antigens|}>} InvValPreviousMonth1)', '|', chr(39));

The replace function returns a string. Thus vTestLet contains a string.

However set analysis expression cannot be used in the script. Also aggregation functions like sum need to be used in load statements together with a group by clause. They cannot be used outside the context of a load statement in the script.

[#5 · Accepted solution]

Gysbert,

This is not the solution I was expecting, but it works.  Thanks for your comments and hints.

John

Solution: Create a new summary table summarizing the detailed source table using a GROUP BY and put the Evaluate(SUM(...) as part of that load.

NoConcatenate

ProductSummaryTable:

LOAD

  'M'                AS PeriodType,

  ProductGroupIMa    AS ProductGroup,

  'InvValue'         AS ValueType,

  Evaluate(SUM(InvValPreviousMonth1)) AS ProductSumMonth,

  $(vOpMgmtNewMonth) AS GlPeriod,

  $(vOpMgmtNowYear)  AS FYear,

  'TEST'             AS CalendarMonth

RESIDENT InvValuationTable

WHERE ProductGroupIMa <> 'Not Categorized'

GROUP BY ProductGroupIMa;