Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I have a list of my total Orders. I will later be adding a column to show if they have been changed or not so to start I add it to begin with and set it to zero for all orders. Let's say it's like the below table.
ORDERS:
LOAD * INLINE [
ORDER_NO, _CHANGED
1, 0
2, 0
3, 0
4, 0
5, 0
];
I also have another table where I have orders that meet a certain criteria. They are in the table CHECK and some have been changed, others haven't.
CHECK:
LOAD * INLINE [
ORDER_NO, _CHANGED
1, 1
2, 1
3, 0
];
If I use left join the column _CHANGED will have the values that are in table ORDERS. I would like my result to have 1's where they have been changed and zeroes where they haven't. I would like to have the resulting table:
1, 1
2, 1
3, 0
4, 0
5, 0
How do I make that work using joins?
(I know one solution would be to create a different column and after a join add ( + ) both columns in a new column but I want to avoid loading it again below. Is it possible?)
In this case it should be quite simple. Use your CHECK table as a mapping table and use applymap
CHECK:
MAPPING LOAD * INLINE [
ORDER_NO, _CHANGED
1, 1
2, 1
3, 0
];
ORDERS:
LOAD
ORDER_NO,
applymap('CHECK',ORDER_NO,_CHANGED) as _CHANGED
INLINE [
ORDER_NO, _CHANGED
1, 0
2, 0
3, 0
4, 0
5, 0
];
In this case it should be quite simple. Use your CHECK table as a mapping table and use applymap
CHECK:
MAPPING LOAD * INLINE [
ORDER_NO, _CHANGED
1, 1
2, 1
3, 0
];
ORDERS:
LOAD
ORDER_NO,
applymap('CHECK',ORDER_NO,_CHANGED) as _CHANGED
INLINE [
ORDER_NO, _CHANGED
1, 0
2, 0
3, 0
4, 0
5, 0
];
Hello Gysbert,
I was stuck in my thinking and didn't realize I could use a map. Thanks for reminding me!
Cheers,
Marcelo