how to set different values forsamecoloumn in different rows

scorpion · Post by **scorpion** » Sat Feb 25, 2006 2:46 am

hi all,

can any one please help me how to set the values of columns for different rows.

i.e.

i need to set a output column value for first row is(default to "x") and from second row onwards(>first row) the value of the same coloumn should be (default to "y")

how can i perform this.

thanks in advance,

kcshankar · Post by **kcshankar** » Sat Feb 25, 2006 3:46 am

Hi,
Try @Outrownum/@Inrownum function to get required output.

regards
kcs

ArndW · Post by **ArndW** » Sat Feb 25, 2006 4:02 am

Use a stage variable in a transform stage. You can either use a initial value that is changed after the first row or a condition which checks the @INROWNUM/@OUTROWNUM as kcshankar suggested to assign the column value depending upon which row is processed.

scorpion · Post by **scorpion** » Sun Feb 26, 2006 12:46 am

hi kcs/ArndW

thanks for your replies,can you please help me out detail syntax.

Example:

for "empno" coloumn, i need to display the value for first row is "x"
and for remaining rows (>1st row),i need to display the value is "y"

can you please give me exact syntax to define in stage variable for above(emp)scenario...

thanks a lot in advance.......

chulett · Post by **chulett** » Sun Feb 26, 2006 12:53 am

Come now.

First off, a stage variable seems overkill for something as simple as this. All you need to do is check @OUTROWNUM to see if it is one or greater than one and set the value in the derivation of the column accordingly.

Are you saying you can't work out the "If Then Else" syntax?

scorpion · Post by **scorpion** » Sun Feb 26, 2006 4:24 am

Thanks! Chullet,

Tried "if then else" with @OUTROWNM in derivation of Transformer,it is working fine.

i need default value for a ouput coloumn like

"Employee= " : link.coloumnname: " , Dept=S_CODE"

Note: link.coloumnname is the value of one input coloumn

so i need display in an output coloumn value like:

"Employee= "inputcoloumnvalue",Dept=S_CODE"

i tried a lot but giving in some other way....
can you please helpme on this...

chulett · Post by **chulett** » Sun Feb 26, 2006 7:44 am

When you are working inside the Derivation editor, there is an 'ellipsis' or three little dots like this (...) that you can click on for help building expressions. One of the operators it offers you is concatenation and in DataStage the concatenation operator is the colon or ':' sign.

You build up combinations of strings and link values by concatenating them together with the colon:

Code: Select all

"Employee= " : link.columnname : ",Dept=S_CODE"

Which just so happens to match what you posted, so (theoretically) should mean it worked just fine for you and you don't actually have a problem after all.

So what "didn't work" when you tried that syntax? Please be as complete and clear as possible, with examples if at all possible.

ArndW · Post by **ArndW** » Sun Feb 26, 2006 10:30 am

It looks as if you used a bad variable name, is it took the value as a literal instead of as a variable name. Please follow Craig's advice and use the drop-down boxes to insert the correct variable name.

ArndW · Post by **ArndW** » Sun Feb 26, 2006 10:59 am

Hmmm... odd. What if you just put in, for test purposes,

"If @OUTROWNUM =1 Then 1 Else DSLink3.PermissionID" and see if that works?

chulett · Post by **chulett** » Sun Feb 26, 2006 11:01 am

You never actually said anything about it generating an error. I just got the impression that your output wasn't quite what you expected.

I'm still not clear on exactly what you are expecting as output. Are the double-quotes supposed to actually be in the field when all is said and done? If that's the case, you'll need to use a combination of single quotes around the outside of the strings so the double-quotes inside the strings just become data.

If that's not the error you are trying to solve, then I'm obviously still confused.

scorpion · Post by **scorpion** » Sun Feb 26, 2006 11:21 am

hey Thanks alot Chullet,

it is working fine...i need to display "(double quotes)in the field value..
as you said i append single quotes before and after the values...

now i got the required out put value...

output is an .csv file but when i open in wordpad i found 3 double quotes synbols instead of one double quotes symbols..why?

kumar_s · Post by **kumar_s** » Sun Feb 26, 2006 11:37 am

Check Field default in Format Tab. The Quotes might be double. Place it Null if you require or just remove the option.

chulett · Post by **chulett** » Sun Feb 26, 2006 11:44 am

scorpion wrote:output is an .csv file but when i open in wordpad i found 3 double quotes synbols instead of one double quotes symbols..why?

More than likely because you have quote delimited string fields in the file with quotes in the field. Then you have to 'quote the quote' in order for it to not confuse things. The important thing is - can the resulting file be read properly by whatever application it is destined for?