Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a Trans table:
Month | InvNo | Item | Ctgy | Val |
Jan | 1 | A | XY | 10 |
Jan | 2 | B | W | 20 |
Jan | 3 | A | XY | 15 |
Jan | 4 | C | W | 12 |
Jan | 5 | A | Z | 20 |
Feb | 6 | A | XY | 15 |
Feb | 7 | A | Z | 17 |
Feb | 8 | B | W | 18 |
Feb | 9 | C | W | 11 |
Feb | 10 | A | XY | 14 |
I have a Rate table:
Month | Rate |
Jan | 0.7 |
Feb | 0.8 |
I use this code to load the rate field into the Trans table,
LEFT Join (Trans)
LOAD Pd,
'A' as Item,
'XY' as Ctgy,
Rate
Resident
EDIT: This should read Resident Rate
Month | InvNo | Item | Ctgy | Val | Rate |
Jan | 1 | A | XY | 10 | 0.7 |
Jan | 2 | B | W | 20 | - |
Jan | 3 | A | XY | 15 | 0.7 |
Jan | 4 | C | W | 12 | - |
Jan | 5 | A | Z | 20 | - |
Feb | 6 | A | XY | 15 | 0.8 |
Feb | 7 | A | Z | 17 | - |
Feb | 8 | B | W | 18 | - |
Feb | 9 | C | W | 11 | - |
Feb | 10 | A | XY | 14 | 0.8 |
This gives me:
NOW, I WANT to Load the Constant 1 as the Rate for any Item A, which has a Ctgy of ‘Z’.
Ie I want this
Month | InvNo | Item | Ctgy | Val | Rate |
Jan | 1 | A | XY | 10 | 0.7 |
Jan | 2 | B | W | 20 | - |
Jan | 3 | A | XY | 15 | 0.7 |
Jan | 4 | C | W | 12 | - |
Jan | 5 | A | Z | 20 | 1.0 |
Feb | 6 | A | XY | 15 | 0.8 |
Feb | 7 | A | Z | 17 | 1.0 |
Feb | 8 | B | W | 18 | - |
Feb | 9 | C | W | 11 | - |
Feb | 10 | A | XY | 14 | 0.8 |
I thought this might work:
LEFT Join (Trans)
LOAD
'A' as Item,
'Z' as Ctgy,
Num(1) as Rate
Resident Trans
But the values stay at ‘-‘
Any ideas ?
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.
-Rob
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.
-Rob
Hi Mazacini,
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.
Regards,
Sokkorn
Hi Rob
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.
Joe
That is correct. Null values will be created for the added fields in all non-matching rows of the join.
-Rob