We need to compare two different files as below and output should be the record no. and the field name which does not match with the before file.
Also deleted records from before file need to be captured.
All fields are Nvarchar & we cannot sort the fields based on key field col1 as sequence is important and comparison should be done in the sequence order only.
before file:
col1, col2, col3
1,2,3
4,5,6
7,8,9
1,1,1
2,2,2
3,3,3
after file:
col1, col2, col3
1,2,3
4,9,6
7,8,8
5,2,2
3,3,3
4,4,4
expected o/p
record 2 , col2 , update
record 3, col3, update
record 4, col1, delete
record 4, col2, delete
record 4, col3, delete
Tried Join, Difference but no luck. Also tried introducing a new col surr key but then will need to make a new join for every field. Or are we left with use of transformer.
Please suggest a design logic.
Compare fields in two different flat files
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 30
- Joined: Tue Sep 08, 2009 4:24 am
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 30
- Joined: Tue Sep 08, 2009 4:24 am
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
you are missing information,
you should create a surrogate key in the before file based on the line number
it will be your key
but in your exemple, why do you say "record 4, col1, delete"
I see it as an update
record 4 before 1,1,1 after 5,2,2
the output could be
record 4, col1, update
record 4, col2, update
record 4, col3, update
Let's say you have
before file:
col1, col2, col3
1,2,3
4,5,6
7,8,9
1,1,1
2,2,2
3,3,3
after file:
col1, col2, col3
1,2,3
4,5,6
7,8,9
5,2,2
1,1,1
2,2,2
3,3,3
how do you know that 1,1,1 is an update or a delete
you should create a surrogate key in the before file based on the line number
it will be your key
but in your exemple, why do you say "record 4, col1, delete"
I see it as an update
record 4 before 1,1,1 after 5,2,2
the output could be
record 4, col1, update
record 4, col2, update
record 4, col3, update
Let's say you have
before file:
col1, col2, col3
1,2,3
4,5,6
7,8,9
1,1,1
2,2,2
3,3,3
after file:
col1, col2, col3
1,2,3
4,5,6
7,8,9
5,2,2
1,1,1
2,2,2
3,3,3
how do you know that 1,1,1 is an update or a delete