Calculating count ONLY on first occurence of each customer

[gateleys]

My input records are as follows, with multiple records for each customer.

Code: Select all

Cust_ID  Cust_Name  Value
1         AA             100
2         BB             200
1         AA             50

Now, for the first occurence of each Cust_ID, I need to multiply Value by 5. However, for subsequent occurence of each customer's ID, I just pass the values as they are. So, my result looks like -

Code: Select all

Cust_ID  Cust_Name  Value
1         AA             500    (after multiplication by 5)
2         BB             1000  (after multiplication by 5)
1         AA             50      (remains as it is)

Please elaborate how I can do this!!

[ArndW]

Hello Gateleys,

the simplest and most efficient method would be to sort the incoming stream by Cust_ID and then to use a stage variable to store the last Cust_ID value and to do a comparison on this value with the current Cust_ID to see if this has changed in order to do your x5 computation.

[PhilHibbs]

Hello Gateleys,

the simplest and most efficient method would be to sort the incoming stream by Cust_ID and then to use a stage variable to store the last Cust_ID value and to do a comparison on this value with the current Cust_ID to see if this has changed in order to do your x5 computation.

I do this a lot. It relies on the fact that stage variables are evaluated in the order that they appear, so if you use the value of one stage variable in the derivation of another stage variable that is above it, then it gets the value from the previous record.

Code: Select all

svKey        | link.Key
svKeyChanged | svKey <> svOldKey
svOldKey     | svKey

I can't remember whether or not svKey is null when the first record is processed. I have seen multiple NULL keys treated as separate key values, so you might want to code around that.

[I_Server_Whale]

There is no necessary to code around that because Cust_Id will never be NULL. I think when the first record is processed, the stage variable (svKey) doesn't hold any value (probably NULL).

So, when you process the first record, always the OLDSVKEY<>SVKEY.

I agree with Arnd, this is the best way to do it!

Naveen.

[chulett]

I think when the first record is processed, the stage variable (svKey) doesn't hold any value (probably NULL).

Don't "think" - know what it is by explicitly setting it's Initial Value in the stage variables property box.

And set it to something non-null that you know will be different than any first value coming in so the first 'not equal' check will be true. Keep in mind the fact that - if the initial value of the stage variable is null - that first not equal check will take the false path.

[gateleys]

THankx Arnd, I got it right.

Hello Gateleys,

the simplest and most efficient method would be to sort the incoming stream by Cust_ID and then to use a stage variable to store the last Cust_ID value and to do a comparison on this value with the current Cust_ID to see if this has changed in order to do your x5 computation.