I'm doing a data conversion with one of my fields (SUMDWK) from one of the tables that will be used in a merge join. With the new, converted field, I do a look up. From this look up, I want to take a new field FiscalWeekOfYear, and replace the original field, SUMDWK. This is necessary because SUMDWK is one of the sorted fields. In the look up, it is not possible to change the Output Alias. Does anybody know a way around this Thanks.
In the merge join transformation, it will join on the originally specified join keys, which would be incorrect for me. So I'm not sure how this would be correct
Lindsay wrote:
In the merge join transformation, it will join on the originally specified join keys, which would be incorrect for me. So I'm not sure how this would be correct
Phil, the merge join is after the lookup.
John, I can't guarantee that the values looked up will already be in order. I've added a sort after the lookup and before the merge join, but I get the error saying I must have at least one sorted column, and they must have matching metadata. I've verified the sort order is still correct on the first data source.
John, I can't guarantee that the values looked up will already be in order. I've added a sort after the lookup and before the merge join, but I get the error saying I must have at least one sorted column, and they must have matching metadata. I've verified the sort order is still correct on the first data source.
Perhaps, the problem is with the data type. Double click in the arrows joining the dataflow elements and check that the data type of the columns used in the join are the same type.
BTW, I don't think that passing the rows trhough a LKP alter the order of the outcoming rows; so if the rows are already sorted before the lkp you may want to save the use of the sort transformation, which is expensive from the performance stand point.
BTW, I don't think that passing the rows trhough a LKP alter the order of the outcoming rows; so if the rows are already sorted before the lkp you may want to save the use of the sort transformation, which is expensive from the performance stand point.
Except that the merge join is being done on the new column provided from the lookup - thus it needs to be sorted.
Phil, I did as you said for the sort transform. The other source (I'll call it source1) did not need a sort; I put one on it and I got the warning that it wasn't necessary because it was already sorted this way. So I removed that sort. What I have is two sources, source1 I leave alone. Source2, after specifying the source, has a data conversion on all three key fields to match the data types of the Source1 fields. These fields are called SUMDYR, STRNUM, SUMDYR. I then have a derived column, which is used in the lookup. In the lookup I need to get a field which should replace a SUMDYR. I then added a sort. All three fields sorted on are "new" fields, as you can see by the field names:
Copy of SUMDYR (data type changed)
Copy of STRNUM (data type changed)
FiscalYear_Name (comes fr lookup)
I then do the Merge join, which gives me the error I stated above. Does the Sort transformation replace the sort specified on the data source It doesn't seem to me that it is doing that for me.
I hope this makes sense.
Rafael,
You were correct in telling me to check the data type. I had done a data conversion on all of the key fields in order to do the look up. However, in order to match the field types in Source1, I needed to convert them back again. However, the suggestion to add another sort before the merge join was also instrumental in getting it to work. I have added a third table, and have been able to join on all three and insert into my staging table. Thank you all so much for your help!