The second join doesn't work becaise the Rate field was created for all rows in the first Join -- and will not match the value for Rate in the second join. The solution is to collect in a temp table all the values and rows you want to join. Use multiple loads and concatenate as required. Then Join that temp table to the Trans table in a single Join.
Thanks for your help in resolving this.
Can you clarify something for me?
I thought my first join joined values per period ONLY FOR ITEM ='A' AND CTGY = 'XY'.
In fact, it joins those values, BUT ALSO CREATES A '-' FOR ALL OTHER VALUES OF ITEM AND CTGY.
Is that right?
Again, thanks for your help.
Not sure I understood you. But let try this solution:
[Rate]: MAPPING LOAD 'A' & Month, Rate SELECT * FROM TableRate; [Tran]: LOAD Month, InvNo, Item, Ctgy, Val, IF(Ctgy='Z',1,APPLYMAP('Rate',Item & Month,'')) AS Rate SELECT * FROM TableTran;
Do let me know if this one can help you.