DSXchange

I am having a source table named PRODUCT .
Its schema is given below.
Examples of data in column PRICE are 80, 70, 170, 90, 70

My intention is to use the looping feature of tranformer stage and create a sub total of the column PRICE for each DISTINCT value of GROUP_CODE column.

I created two stage variables and used the following experssions for them :

PRICESUM=If LastRowInGroup(DSLink4.GROUP_CODE) Then 0 Else PRICESUM + DSLink4.PRICE
PRICETOTAL=If LastRowInGroup(DSLink4.GROUP_CODE) Then PRICESUM + DSLink4.PRICE Else 0

The datatypes for the above stage variables are Varchar.

The problem I am facing is that the values of these stage variables are not getting evaluated properly.


Can anyone please help me on this ?

Thanks
Kumarjit.

Arithmetic operations on varchars is not right approach. convert them into integers using AsInteger() and even before that, check if they can be converted into integers using num() .

Hi Kumarjit,

ad 1: you are not using the looping-feature of the transformer stage, but the key-break-detection feature, which is not the same.

ad2: datatype Varchar for the stage variables is false. DataStage can't do arithmetic operations using Varchar. Use decimal-datatype and correct conversion-functions.

ad3: you initialize PRICESUM with 0 on LastRowInGroup and then you calculate PRICETOTAL as PRICESUM + DSLink4.PRICE. This is wrong.
You need to change order of execution and initialize PRICESUM with 0 on the outset.

PRICETOTAL = PRICESUM + DSLink4.PRICE
PRICESUM: If LastRowInGroup(DSLink4.GROUP_CODE) Then 0 Else PRICETOTAL

The LastRowInGroup-function on PRICETOTAL can be omitted.
Instead you should use the function on the transformers link-constraint, so that only the last row per group is output to the link.

Perhaps I'm missing something, but why not use Aggregator stage? With Zulfi's warning about converting to numbers, Aggregator will do what you need.