Join tables - duplicate values

[Anonymous]

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!

[Anonymous · Accepted solution]

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]