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.
sequence number
Moderators: chulett, rschirm, roy
Try this,
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.
Code: Select all
counter: if (TRIM(X):TRIM(Y):TRIM(Z)) = prevVal then counter + 1 else 1
prevVal: TRIM(X):TRIM(Y):TRIM(Z)
Also make sure the incoming data is sorted on X, Y and Z, in that order, ascending.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
-
- Charter Member
- Posts: 822
- Joined: Sat Sep 17, 2005 5:25 pm
- Location: USA
sequence
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.
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.
DSguru2B wrote:Try this,Use the counter as the derivation of nO.Code: Select all
counter: if (TRIM(X):TRIM(Y):TRIM(Z)) = prevVal then counter + 1 else 1 prevVal: TRIM(X):TRIM(Y):TRIM(Z)
Also make sure the incoming data is sorted on X, Y and Z, in that order, ascending.
I guess DSGuru meant:
Just a typo
Code: Select all
counter: if (TRIM(X):TRIM(Y):TRIM(Z)) = prevVal then counter else counter+ 1
prevVal: TRIM(X):TRIM(Y):TRIM(Z)
Success consists of getting up just one more time than you fall.
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)
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.