Join tables - duplicate values

Anonymous — Fri, 10 Mar 2017 16:34:46 GMT

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

Anonymous — Fri, 10 Mar 2017 17:47:55 GMT

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]

topic Re: Join tables - duplicate values in App Development

Join tables - duplicate values

Re: Join tables - duplicate values