Moving one column values to other in the same table

Question

Moving one column values to other in the same table

sachinvinod2k

Mr or Mrs. 500

Points: 581
More actions
May 28, 2009 at 7:46 am

#132380

Hello All
Let me give a brief description of the problem.
I have two columns Sex and DOB.
The sex column has values Male, Female and 01/02/2003,02/12/2001. The dates should go under the DOB column where the corresponding values are Null.
I would like to move those two Date values from Sex column into the Date column.
I tried multicast by redirecting to two outputs and joining them again but its causing data duplication. Will it work by using a conditional split on date ?
Please help me in this.
Thanks
Kou

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246993 More actions · Answer 1

Maybe add a couple of derived columns, cSex and cDOB (or whatever) which are set correctly and use these.

If there's not enough flexibility in there, a script component could easily do the tweaking for you, fixing the contents of the fields on the way through.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

sachinvinod2k Mr or Mrs. 500 Points: 581 More actions · Answer 2

I can add derived columns but there are so many such values and so its tough to change all of them. do you know how to use conditional split and then join the two seperate tables back ?

Phil Parkin SSC Guru Points: 246993 More actions · Answer 3

sachinvinod2k (5/28/2009)
I can add derived columns but there are so many such values and so its tough to change all of them. do you know how to use conditional split and then join the two seperate tables back ?

I do not believe that it would be necessary to do that. We can fix your data as it travels along the pipeline, one way or another, without needing to split it.

Can you post some sample data which illuminates your problem?

What do you mean by "many such values"? If you build the fix correctly, it does not matter. Or do you mean many such fields?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

sachinvinod2k Mr or Mrs. 500 Points: 581 More actions · Answer 4

Name SSN AGE SEX Race DOB

Person212345678924Male10/23/2006NULL

Person445678934227Female02/12/2001NULL

Person1AB34213459823MaleWhite1983-04-12 00:00:00.000

Person3CND23145125FemaleAsian2008-12-22 00:00:00.000

Person5CKF354623836MaleHisp2001-01-01 00:00:00.000

Person633454622134MaleLatino2003-02-02 00:00:00.000

I want to move those dates from Race column to corresponding DOB where its empty. I have just shown for two values. There are many other values like that which are totally shifted or misplaced.

Please suggest something.

Thanks

Kou

Phil Parkin SSC Guru Points: 246993 More actions · Answer 5

Yuk, I am amazed that anyone would provide you with a file of this quality.

Is it true that your field delimiter is a space? But then that there is also a space between DOB (date-part) and DOB (time-part)?

The first thing I would do is complain vigorously to the creator of this rubbish.

Assuming you have already done that 😉 ... I would use a Script Component to try and sort out this mess. Just read the data values in as a single string per row and break into individual columns in the script.

Or maybe use

Name SSN AGE SEX everything_else

If the first 4 fields are always present - then just use script to split out the 'everything_else' bit.

Good luck.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

sachinvinod2k Mr or Mrs. 500 Points: 581 More actions · Answer 6

Hi

Thanks for you reply.

This is not the true file. I just pasted the file from the table, and i guess that is they it has spaces in between them. The original file is even more worser with bad delimiters and junk characters. Its taking one helluva time for me to data clean them.

I would try that and get back to you.