Comparing data in the columns

sandeepgs · Post by **sandeepgs** » Fri Oct 31, 2008 3:55 am

Hi,

I have an issue with the data comparison

Totally I have 30 columns. Data in all the fields should be unique.

I tried to achieve this using transformer

ex:

Trim(DSLink6.ACC_NBR) <> (Trim(DSLink6.BAL_AMT) Or Trim(DSLink6.BAL_EFF_DATE) Or Trim(DSLink6.BAL_END_DT) Or Trim(DSLink6.WTHD_AMT))

I cannot effort to have separate not equal conditions. so I combined everything. But the logic i had written is not working. It is passing all the records even if there is matching values.

Is there any other way of achieving this condition.

mdbatra · Post by **mdbatra** » Fri Oct 31, 2008 3:59 am

Observe carefully...U have compared just the First column with the rest...where u checking the in-between cols with each other!

sandeepgs · Post by **sandeepgs** » Fri Oct 31, 2008 4:12 am

mdbatra wrote:Observe carefully...U have compared just the First column with the rest...where u checking the in-between cols with each other!

S actually I noticed it but how can i achieve it. If I go for multiple not equal conditions the querry will become huge. So is there any other Idea of achieving this.

Is there any built in function available for achieving this.

mdbatra · Post by **mdbatra** » Fri Oct 31, 2008 4:23 am

Make stage variable for every column & write its derivation as Trim(col_name) or whatever u need.

Now in output col derivation, compare the input_col with all the stage variables except the one defined for that particular column itself.

devesh_ssingh · Post by **devesh_ssingh** » Fri Oct 31, 2008 4:43 am

easiest logic when you are comapring columns,

Creat Two stage variable

SVPrev
SVcur

assigned SVcur to SVprev and
now,in SVcur concat all the coulmns which you want to compare
then
set Svflag= if SVPrev<>SVcur then T else F

in this way you will be able to compare all the columns at a time.
then on flag gettin set on TRUE or FALSE you can do further events.

Hope it will help you!!!

cheers!!!
Devesh

mdbatra · Post by **mdbatra** » Fri Oct 31, 2008 4:46 am

Devesh..u r concatenating all cols...
Are all the cols having same datatype?

ArndW · Post by **ArndW** » Fri Oct 31, 2008 5:14 am

(Original post retracted - I misread and misunderstood the question)

Are all the columns numeric of the same type?

mdbatra · Post by **mdbatra** » Fri Oct 31, 2008 5:21 am

devesh..if u observe carefully, ur logic will compare records( previous & current) & not the columns ..

sandeepgs · Post by **sandeepgs** » Fri Oct 31, 2008 5:34 am

mdbatra wrote:Devesh..u r concatenating all cols...
Are all the cols having same datatype?

S all the columns are of same data type (Char).

sandeepgs · Post by **sandeepgs** » Fri Oct 31, 2008 5:35 am

mdbatra wrote:Devesh..u r concatenating all cols...
Are all the cols having same datatype?

S all the columns are of same data type (Char).

mdbatra · Post by **mdbatra** » Fri Oct 31, 2008 5:59 am

even if they are same type, concatenation is not the solution. U would ultimately result into
1. Record Comparison(instead of column)
2. Faulty results( as stated by ArnDW)

sandeepgs · Post by **sandeepgs** » Fri Oct 31, 2008 6:28 am

devesh_ssingh wrote:easiest logic when you are comapring columns,

Creat Two stage variable

SVPrev
SVcur

assigned SVcur to SVprev and
now,in SVcur concat all the coulmns which you want to compare
then
set Svflag= if SVPrev<>SVcur then T else F

in this way you will be able to compare all the columns at a time.
then on flag gettin set on TRUE or FALSE you can do further events.

Hope it will help you!!!

cheers!!!
Devesh

Hi Devesh,

As per my undrestanding. IT goes this way

Ex:
Col1 Col2 Col3
sa ds fd

If this is my data

The stage variable will get evaluated this way

col1||col2||col3 = SVcur
SVcur = SVPrev
Svflag= if SVPrev<>SVcur then T else F

so finally it will result in F for all the records.
It goes this way

sadsfd= SVcur
sadsfd = SVPrev
Svflag= if sadsfd <> sadsfd then T else F

So it will be F always. Correct me If I was wrong.

Thanks.

sandeepgs · Post by **sandeepgs** » Fri Oct 31, 2008 7:11 am

mdbatra wrote:Make stage variable for every column & write its derivation as Trim(col_name) or whatever u need.

Now in output col derivation, compare the input_col with all the stage variables except the one defined for that particular column itself.

Hi Batra,

As you said If I create stage variable for 30 col and compare each col with all the stage variables (Derivations). As I undrestood it is same as writing a constraint comparing all the columns . So i think that it is same but we are writing a derivation insted of constraint.

So what I think is it is better to use constraint instead of stage variable because stage variables willl effect performance. But as I told you if I go for constraint it is increasing my querry size.

Correct me if I was wrong..

ray.wurlod · Post by **ray.wurlod** » Fri Oct 31, 2008 8:44 am

Topic Relocated per request - Andy

chulett · Post by **chulett** » Fri Oct 31, 2008 9:19 am

Moderator please move to the, oh I don't know... General forum.