Page 1 of 1
Calculating count ONLY on first occurence of each customer
Posted: Mon Oct 03, 2005 12:49 am
by 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!!
Posted: Mon Oct 03, 2005 1:25 am
by 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.
Posted: Mon Oct 03, 2005 8:12 am
by PhilHibbs
ArndW wrote: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.
Posted: Mon Oct 03, 2005 8:39 am
by 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.
Posted: Mon Oct 03, 2005 9:49 am
by chulett
naveendronavalli wrote: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.
Posted: Mon Oct 03, 2005 9:55 am
by gateleys
THankx Arnd, I got it right.
ArndW wrote: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.