Is it possible to use the values accross the records?

kvnbabu · Post by **kvnbabu** » Fri Nov 02, 2007 2:59 am

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?

balajisr · Post by **balajisr** » Fri Nov 02, 2007 3:08 am

Welcome to DSXChange.

Your clue is stage variables in the transformer stage. Try it and post if you need furthur help.

kvnbabu · Post by **kvnbabu** » Fri Nov 02, 2007 3:26 am

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?

balajisr · Post by **balajisr** » Fri Nov 02, 2007 3:42 am

Please post your code.

chulett · Post by **chulett** » Fri Nov 02, 2007 6:46 am

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.

kvnbabu · Post by **kvnbabu** » Sat Nov 03, 2007 3:21 am

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.

chulett · Post by **chulett** » Sat Nov 03, 2007 6:23 am

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.

JoshGeorge · Post by **JoshGeorge** » Sat Nov 03, 2007 10:37 am

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

kvnbabu wrote:My expected output should be:

Col1 Output
1 1
2 2
4 2
5 3
6 4
8 4

kvnbabu · Post by **kvnbabu** » Mon Nov 05, 2007 12:46 am

In the first statement svCurr is used which is not defined till now and is done in the next statement. I was under the impression that it is defined so Datastage throws error.

Thanks for the help, its working.