Comparison
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 248
- Joined: Fri Jul 30, 2010 9:04 am
Comparison
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
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
Regards,
Kenny
Kenny
-
- Participant
- Posts: 248
- Joined: Fri Jul 30, 2010 9:04 am
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
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
Regards,
Kenny
Kenny
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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)
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)
-
- Participant
- Posts: 248
- Joined: Fri Jul 30, 2010 9:04 am
@ 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
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
Regards,
Kenny
Kenny
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...
If they have to have a different value then a bit more complex...
-
- Participant
- Posts: 248
- Joined: Fri Jul 30, 2010 9:04 am
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
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
-
- Participant
- Posts: 248
- Joined: Fri Jul 30, 2010 9:04 am
-
- Participant
- Posts: 248
- Joined: Fri Jul 30, 2010 9:04 am
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.
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.
Regards,
Kenny
Kenny