Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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]
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]