Is it possible to use the values accross the records?
Moderators: chulett, rschirm, roy
Is it possible to use the values accross the records?
Hi,
I need to define a variable so that I increment its value by 1 conditionally and should be able to access the value across records?
For example, out of 10 incoming records, I want to know how many records are of value > 10. Then I define a variable called Count and set to 0 initially. For every record I check the condition, and increment by 1.
Can anybody give me a clue?
I need to define a variable so that I increment its value by 1 conditionally and should be able to access the value across records?
For example, out of 10 incoming records, I want to know how many records are of value > 10. Then I define a variable called Count and set to 0 initially. For every record I check the condition, and increment by 1.
Can anybody give me a clue?
Appreciate your prompt reply.
I have tried that, but it didn't seem to be working out for me.
Say I have 10 records like follows:
Col1
1
2
3
4
6
In the above, the sequence is missing after the value 4. Here I need to increment the value of SV1 by 1 if it is continous, other it should not.
I tried doing the same using stage variable but somehow I am ending up with having 1 for each and every record. I dont seem to get my logic correct.
My expected output should be:
Col1 Output
1 1
2 2
4 2
5 3
6 4
8 4
Can you help me?
I have tried that, but it didn't seem to be working out for me.
Say I have 10 records like follows:
Col1
1
2
3
4
6
In the above, the sequence is missing after the value 4. Here I need to increment the value of SV1 by 1 if it is continous, other it should not.
I tried doing the same using stage variable but somehow I am ending up with having 1 for each and every record. I dont seem to get my logic correct.
My expected output should be:
Col1 Output
1 1
2 2
4 2
5 3
6 4
8 4
Can you help me?
How did we get from needing to 'know how many records are of value > 10' to checking a sequence for contiguous numbers?
Your example is good to post, but please explain your requirements in words rather than expect people to infer it correctly from your example.
Your example is good to post, but please explain your requirements in words rather than expect people to infer it correctly from your example.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
I will explain my exact requirement:
I have one key in the source which will be in sequence like 1,2,3, etc. When I pull the data to DW, I need to ensure the sequence is not missed out. So first thing I have to find out, what is the max value of the key avaibale in target. Then I read the data and ensure that it follows the sequence.
In the Target, if I have 3 records with 1,2,and 3, then I sould ensure that the data I pull from source should start with 4 and rest of records' values must be in sequence. meaning 4,5,6 etc...
Incase the values in source are 4,5,6, 8, 11,12,13... Then 7 and 9 are missing and this is eaxctly what I should ensure and give message aprropriately.
I have one key in the source which will be in sequence like 1,2,3, etc. When I pull the data to DW, I need to ensure the sequence is not missed out. So first thing I have to find out, what is the max value of the key avaibale in target. Then I read the data and ensure that it follows the sequence.
In the Target, if I have 3 records with 1,2,and 3, then I sould ensure that the data I pull from source should start with 4 and rest of records' values must be in sequence. meaning 4,5,6 etc...
Incase the values in source are 4,5,6, 8, 11,12,13... Then 7 and 9 are missing and this is eaxctly what I should ensure and give message aprropriately.
Define what 'and give message appropriately' means in your last paragraph. How is this 'message' sent? What happens other than this message, is the load failed or do you load it and send a message noting 'the sequence was not in sequence but I re-sequenced it for you, no need to thank me.'?
What happened to this bit, something you didn't mention this time: "In the above, the sequence is missing after the value 4. Here I need to increment the value of SV1 by 1 if it is continuous, other it should not.". Is that rule still in force or are you literally just trying to detect sequence gaps?
As noted earlier, post your 'code' - your stage variable derivations.
What happened to this bit, something you didn't mention this time: "In the above, the sequence is missing after the value 4. Here I need to increment the value of SV1 by 1 if it is continuous, other it should not.". Is that rule still in force or are you literally just trying to detect sequence gaps?
As noted earlier, post your 'code' - your stage variable derivations.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 612
- Joined: Thu May 03, 2007 4:59 am
- Location: Melbourne
Sort and partition your records by key field\s so that matching rows are adjacent to each other and on the same node in the transformer. Assign stage variables in below order (Note: Stage variables are processed in order)
svPrev = svCurr
svCurr = Input.Col1
svOutput (Default Value set to 1)= If svCurr = svPrev+1 Then svOutput+1 Else svOutput
svPrev = svCurr
svCurr = Input.Col1
svOutput (Default Value set to 1)= If svCurr = svPrev+1 Then svOutput+1 Else svOutput
kvnbabu wrote:My expected output should be:
Col1 Output
1 1
2 2
4 2
5 3
6 4
8 4
Joshy George
<a href="http://www.linkedin.com/in/joshygeorge1" ><img src="http://www.linkedin.com/img/webpromo/bt ... _80x15.gif" width="80" height="15" border="0"></a>
<a href="http://www.linkedin.com/in/joshygeorge1" ><img src="http://www.linkedin.com/img/webpromo/bt ... _80x15.gif" width="80" height="15" border="0"></a>