Loading from a Resident table into a Resident Table

[#1]

Hi,

I'm having an issue where i have to load from a Resident Table using an if statement to create new fields. Creating the new fields is fine but i can't join them back to the original table without creating duplicate rows.

My Code is as follows;

DRSALES:
LOAD [Invoice Number],
     [Order Number],
     [Item Code] as [Item Code],
     [Invoice Number] & '-' & [Item Code] as [Credit Line Identifier],

     [Quantity Invoiced],
     [Item Major],
     [Item Minor], 
FROM

(qvd)

WHERE [Item Minor] <> 'CAT'
OR [Customer Code] <> 'FOCCC'
OR [Quantity Invoiced] <> 0 //To exclude double counting where customer uses consolidated invoices
OR NOT IsNull([Invoice Number])
;

//DRSALES2//
//This shows Credits Detail
Join LOAD [Invoice Number],
     [Transaction Type],
     [Credit Reason],
     [Invoice Value],
     [Invoice Charges]
FROM

(qvd)
;

//If A Return has been raised and not JUST as a Credit, the Order Number Field will show a Returns Numbers Rather than the Original Invoice number (confusing I know!!)

OriginalReturnsNos:
Join LOAD
     [Original invoice number] & '-' & [Item code] as [Credit Line Identifier],
     [Return number] & '-' & [Item code] as [Return Line Identifier]
FROM
[..\..\..\..\Data\File_Extracts\System21_Extracts\QLKRETURNS.CSV]
(txt, codepage is 1252, embedded labels, delimiter is ',', msq)
Where Exists([Order Number],[Return number])
;

//This Creates a field called CreditLineID which holds the Return Line Identifier (Return number & Item Code) or if that doesn't exist,
// the Credit Line Identifier (Invoice number & Item Code). The 2 fields are then dropped and the CreditLineID field is then used to link back to the Credit Lines when they're reloaded in table Credit Transactions

HERE IS THE BIT THAT DOESN'T WORK.

Basically, now that the Original Return Numbers table has been loaded into DRSALES, i need to do an if statement (as below) to create a field and then load that back into DRSALES. Heres the code im using.

Left Join(DRSALES)
Load
[Credit Line Identifier],
If(IsNull([Return Line Identifier]),[Credit Line Identifier],[Return Line Identifier]) as CreditLineID,
If(IsNull([Line Value]) AND IsNull([Line Cost]) AND [Invoice Charges] > 0, 'Consolidated Invoice',null()) as [Consolidated Check]
Resident DRSALES;

However, this duplicates row (as seen by the frequency of 1 invoice increasing from 3,000 to 200,000

Am i missing something really obvious?

[chematos · Accepted solution]

You should use Load Distinct when you think could be duplicate rows. Could you check if something changes using Distinct??

[#3]

Hi Jose,

Yep, knew it was something obvious.

Thanks,

Nick

[chematos]

It´s a pleasure could be usefull,

Regards,

Chema