2 Replies Latest reply: Dec 8, 2015 3:22 AM by bruno bertels RSS

    Conditional import from one table based on data in another

      Hi all,

       

      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?

       

      I HOPE I explained that well enough... thanks!

       

      Jonathan