Comparison

kennyapril · Post by **kennyapril** » Wed Jun 20, 2012 10:34 am

There is a field which has 4 digits coming from the source.

In a transformer I was trying to compare the first record and next record that can be any record.Once I compare if they are different I should
exchange their number.

I used stage variable and stored the record as prev and then appended another sv and that is the curr but not working.

So in the target none of them should have their same numbers for the field but should have some one else number when compared to the data in source .

Please provide me input with changes

kennyapril · Post by **kennyapril** » Wed Jun 20, 2012 3:04 pm

Just to be clear below is an example

The input is

Field1 ABC, DEF, GHI, JKL
Field2 123, 456, 789, 523

The output required is
Field1 ABC, DEF, GHI, JKL
Field2 789, 123, 523, 456

Nothing but each person for field1 should have anothers persons number but not his number , this should happen for all of them (1 million).

Thanks

ray.wurlod · Post by **ray.wurlod** » Wed Jun 20, 2012 5:03 pm

A horribly inefficient method would be to perform a "not equals" self-join in the source and return the first row from that. You could not use a Join or Lookup stage for that; it would have to be done in source.

Kryt0n · Post by **Kryt0n** » Wed Jun 20, 2012 5:18 pm

Can honestly say the example makes it no clearer to me... I'm guessing you just want to swap field2 values around, doesn't matter where they go.

Is the comparison against Field1 or Field2? (If field2, then forget comparing as a swap would give the same result)

If on 8.5 up, you could (potentially) use the loop variables, at row 1, save Field2 into a stage variable and then save all records (SaveInputRecord). Once all have been saved, start pulling them back, the first record back should be the last record in the stream, save its field2 and in the output, give it the originally saved Field2 record (the value saved off at the beginning off row 1). then for each subsequent row, give the Field2 from the previous record. You will have to read the help files on how to handle such but sounds like what you want (to me at least)

kennyapril · Post by **kennyapril** » Wed Jun 20, 2012 6:48 pm

@ Ray
So is it something like
SELECT table1.field1,table2.field2
FROM table.table1, table.table2
WHERE table1.field2!=table2.field2

But the thing is I need to create a transitional file which contains all the shuffled field2 for the respective field1 and this transitional file should be used to do join with the sources and load the shuffled data.

Please suggest will this meet my requirement

@Kryt0n

That is what exactly what I need is swap field 2 values but the value should be taken randomly from the records and none of them should have the same field2 as the source.

Our version is 8.5 and we have looping but did not understand the logic which you gave. Is there a function to save all the input records?

Please suggest

Kryt0n · Post by **Kryt0n** » Wed Jun 20, 2012 7:26 pm

There is such a function - SaveInputRecord. The help files will tell you how to use it. The process won't quite be random as such since it follows an ordered process and can't guarantee it won't have the same value (unless the Field2 values are all unique)

If they have to have a different value then a bit more complex...

kennyapril · Post by **kennyapril** » Wed Jun 20, 2012 8:22 pm

All the field2 values are unique, so can I use the function?

From the below coding what I understand is save the first field2 record into a stage variable and then SaveInputRecord into the loop.

Is that right?

Kryt0n · Post by **Kryt0n** » Wed Jun 20, 2012 9:02 pm

Give it a try and see. I actually found the help files when reading up on it reasonably good so it will explain how you can go about it.

Your requirements are to hold the first Field2
Save all records until you reach the end
Once all saved start reading them back
Save the new field2
Use either the original Field2 (the one from the stage variable) for the first output row or the newly saved field2 for all subsequent records

Can't tell you exactly the steps needed as was a while since I looked at it but am sure the help files have something of the sort

kennyapril · Post by **kennyapril** » Wed Jun 20, 2012 9:31 pm

Will try it with help files

Thanks very much!

kennyapril · Post by **kennyapril** » Thu Jun 21, 2012 2:55 pm

I found 4 types of loop in the help material, mentioned below.

Loop example: converting a single row to multiple rows
Loop example: multiple repeating values in a single field
Loop example: generating new rows
Loop example:aggregating data

Can you please suggest which one suits better my requirement, for swaping randomly the records in a particular field which one could be better.