2 Replies Latest reply: Jul 2, 2015 2:23 AM by Jonathan Dienst RSS

    Keep left from multiple files

      Hi all,

       

      I'm trying load complimentary data from multiple files keeping the records that exist on a previously loaded table:

       

      Script

       

      Transactions:

      Load *;

      select * from transactions;

       

      Feedback:

      LEFT KEEP (Transactions) LOAD transaction_id as trans_id,

          disposition

          FROM [lib://feedback files/*.txt]

      (txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

       

      Output

      ...

      Feedback << pwmb_dispositions 06-24

      Lines fetched: 238

      Feedback-1 << pwmb_dispositions 06-26

      Lines fetched: 133

      Feedback-2 << pwmb_dispositions 06-27

      Lines fetched: 116

      Feedback-3 << pwmb_dispositions 06-28

      Lines fetched: 145

      Feedback-4 << pwmb_dispositions 06-29

      Lines fetched: 30

      ...

      $Syn 1 = trans_id+disposition

       

      The problem is a synthetic key is generated. Some conclusions:

      1. If I remove the "LEFT KEEP (Transactions)" the data from the files are loaded fine (without the synthetic key) but not filtered.
      2. If I remove the "*.txt" and load a single file, the synthetic key is not generated and the data is filtered.


      Is there any other way to achieve my goal?


      Thanks

        • Re: Keep left from multiple files
          sunil kumar

          If you want to filter data use exits functions end of the table like based but transaction_id should exists in Transactions

           

          Transactions:

          Load *,;

          select * from transactions;

           

          Feedback:

          LOAD transaction_id as trans_id,

              disposition

              FROM [lib://feedback files/*.txt]

          (txt, codepage is 1252, embedded labels, delimiter is '\t', msq) where exsits(transaction_id);

          • Re: Keep left from multiple files
            Jonathan Dienst

            The Keep option prevents the files from automatically concatenating and you cannot force concatenation with the concatenate keyword. You can load the files using

             

            Where Exists(transaction_id)

             

            in the load statement.