2 Replies Latest reply: Aug 26, 2014 4:02 AM by SIVASUBRAMANIAN KNK RSS

    Exists Condition

      Hi all,

      I have confusion while bringing the Exists Condition in my script.

      My script contains both Sales and Stock Data.

      Stock contains 6 records and in Sales 5 records.

      So I have to create a dummy records(6th record) in Sales and should consider weight as zero for that dummy record.

      I created the script, but somewhere wrong in Exists Condition.

      So Kindly help me to solve this issue. Please find the attachment.

       

      Regards,

      Sivasu

        • Re: Exists Condition
          Jonathan Dienst

          Hi

           

          My understanding is that you want to populate a dummy record for a key combination that exists in the stock table, but not in the sales table. To do this, add a SalesKey (which exists only in the Sales table) and use this for the Exists. The final load should be from the Stock source or the STOCK resident table as this data does not exist in the Sales source:

           

          SALES:

          LOAD

            CODE as SalesCode,

            CATEGORY as SalesCategory,

            COMPANY as SalesCompany,

            WEIGHT as SalesWeight,

            COMPANY&'-'&CATEGORY&'-'&CODE as Key,

            COMPANY&'-'&CATEGORY&'-'&CODE as SalesKey

          FROM

          [C:\Users\Sivasubramanian.IBIZ-INDIA\Desktop\Exists.xlsx]

          (ooxml, embedded labels, table is SALES);

           

          Concatenate(SALES)

          LOAD Distinct

            StockCode As SalesCode,

            StockCategory As SalesCategory,

            StockCompany as SalesCompany,

            0 as SalesWeight,

            StockCompany&'-'&StockCategory&'-'&StockCode as Key

          Resident STOCK

          Where Not Exists(SalesKey, StockCompany&'-'&StockCategory&'-'&StockCode);

           

          DROP Field SalesKey; // not needed any more

           

          HTH

          Jonathan

          • Re: Exists Condition

            Thanks Jonathan.