DSXchange

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.

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

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.

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.

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

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

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

Are all the columns numeric of the same type?

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

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 wrote:Devesh..u r concatenating all cols...
Are all the cols having same datatype?

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

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)

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.

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..

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