1 Reply Latest reply: Mar 10, 2017 12:47 PM by Jonah Van Quekelberghe RSS

    Join tables - duplicate values

    Jonah Van Quekelberghe

      Hello,

       

      I have a table where I do i add and adjust data in the load script but it that causes duplicate lines  in certian cases (only if for a unique reference there are multpiple lines, if not there is no duplicate).

       

      What am I doing wrong? Is JOINing not the answer?

       

       

      //     (1) load base table

      Trans_Value:

      LOAD

               "Transaction Reference",

               "Transaction revenue type"

               "Value"

      FROM [source_file_transaction]

       

      //     (2) add extra data from another table

      LEFT JOIN ([Trans_Value])

      LOAD

               "Transaction Reference",

               "Transaction type"

      Resident [Trans_Type];

       

      //     (3) based on extra data, modify "Value 1"

      LEFT JOIN ([Trans_Value])

      LOAD

               "Transaction Reference",

               if("Transaction type" = 'Annulation' , 0 , "Value" ) as "Value Corrected",

               "Transaction Type"

      Resident [Trans_Value];

       

      => For all "Transaction Reference" with more than one line (due to different revenue types) i'll get a duplicated line for each original line.

       

      Thanks in advance!

        • Re: Join tables - duplicate values
          Jonah Van Quekelberghe

          In the mean time I solved my issue

           

          Instead of using a join in the third step i created a temp table for Trans Value and then modified the data

           

           

          //     (1) load base table

          Temp_Trans_Value:

          LOAD

                   "Transaction Reference",

                   "Transaction revenue type",

                   "Value"

          FROM [source_file_transaction]

           

          //     (2) add extra data from another table

          LEFT JOIN ([Temp_Trans_Value])

          LOAD

                   "Transaction Reference",

                   "Transaction type"

          Resident [Trans_Type];

           

          //     (3) based on extra data, modify "Value 1"

          Trans_Value:

          LOAD

                   "Transaction Reference",

                   "Transaction revenue type",

                   "Value",

                   if("Transaction type" = 'Annulation' , 0 , "Value" ) as "Value Corrected",

                   "Transaction Type"

          Resident [Temp_Trans_Value];

           

          Drop Table [Temp_Trans_Value]