Conditional import from one table based on data in another
I have a problem that should, I believe, be relatively simple but I'm such a beginner that I'm sure I'm missing something.
I am importing data from two tables in my LOAD statement;
ITEM, which has product number, category, cost, etc.
TRANSACTION, which details movement of products. Two of its fields are TRANSFER-TO and TRANSFER-FROM. If the TRANSFER-TO field is SCRAP, then this is money lost to the scrapping process, and the total of all entries with this condition would be total lost to scrap. However, sometimes items can be transferred back from SCRAP into other departments, in which case the COST, though listed as positive in the ITEM table, should be subtracted from the calculated total scrapped amount.
If everything was going to be imported from one table, I'd just put an IF statement in the load making COST negative when TRANSFER-FROM was SCRAP, and then make the total the sum of the transactions. However, the COST is only from the ITEM table. How do I modify the data coming from that table based on a condition in a linked table?
Re: Conditional import from one table based on data in another
Usually when i need to compare data from two tables without wanting to join them do i normally use mapping load. which creates a temporary table that can be referenced and give you additional data or instance item cost.
it could look something like this:
In the load statement where you need additional data would i call for that temporary table and will be able to insert additional data based on a field value that is matched.
applymap('Map_Table,ItemNo) as ItemCost, //this will give you the cost for every itemNo.