sequence number

[adams06]

Hi,

here is the input data:

X Y Z
A B C
A B C
A B C1
A B C1
A B C2
A B C2
A B C3


i need to generate the no

For the first two records i need to generate 1 in column no
For the 3rd and 4th records i need to generate 2 in column no
For the 5th and 6th records i need to generate 3 in column no
For the 7th records i need to generate 4 in column no

when ever there is a change in z then the no should change.

my out put should look like this

X Y Z nO
A B C 1
A B C 1
A B C1 2
A B C1 2
A B C2 3
A B C2 3
A B C3 4

I HAVE WRITTEN THIS CODE ITS NOT GIVING THE EXPECTED RESULT

IF RowProcCompareWithPreviousValue(TRIM(X):TRIM(Y):TRIM(Z)) THEN test1 ELSE 1 STAGEVARIABLE
STAGEVARIABLE+1 test1


Can some one help me out.


Thanks in Advance.

[DSguru2B]

Try this,

Code: Select all

counter: if (TRIM(X):TRIM(Y):TRIM(Z)) = prevVal then counter + 1 else 1
prevVal: TRIM(X):TRIM(Y):TRIM(Z)

Use the counter as the derivation of nO.
Also make sure the incoming data is sorted on X, Y and Z, in that order, ascending.

[us1aslam1us]

Hi,

why not just checking the final value(Z) instead of all three.

Sam

[DSguru2B]

Thats true. The OP can just check for change in the col Z. It should be ok as long as its sorted properly. I just translated the OP's code in which he is checking for all the three columns.

[adams06]

Hi DSguru,

i have used the code that is mentioned, but it is not giving me the expected result

The result looks like this

X Y Z nO
A B C 1
A B C 2
A B C1 1
A B C1 2
A B C2 1
A B C2 2
A B C3 1

i am supposed to get

X Y Z nO
A B C 1
A B C 1
A B C1 2
A B C1 2
A B C2 3
A B C2 3
A B C3 4

suggest me


Thanks in advance.

Try this,

Code: Select all

counter: if (TRIM(X):TRIM(Y):TRIM(Z)) = prevVal then counter + 1 else 1
prevVal: TRIM(X):TRIM(Y):TRIM(Z)

Use the counter as the derivation of nO.
Also make sure the incoming data is sorted on X, Y and Z, in that order, ascending.

[loveojha2]

I guess DSGuru meant:

Code: Select all

counter: if (TRIM(X):TRIM(Y):TRIM(Z)) = prevVal then counter else counter+ 1 
prevVal: TRIM(X):TRIM(Y):TRIM(Z) 

Just a typo

[DSguru2B]

Thanks loveojha2. For some reason i thought it was to be incremented for the same group , but if it needs to remain the same then yes, you fixed my code. Just a small change is required in this one. The first row needs to be handled as it has nothing to be compared against and the stage variable 'counter' needs to be defaulted to 1.

Code: Select all

counter: if (TRIM(X):TRIM(Y):TRIM(Z)) = prevVal OR @INROWNUM = 1 then counter else counter+ 1 
prevVal: TRIM(X):TRIM(Y):TRIM(Z)