Duplicate lines doubling amount in script due to left join

bvendeza — Wed, 26 Mar 2025 15:14:20 GMT

Hello!

I'm having a slight issue with the output my script is producing and hopefully, someone has an idea as to how to tackle this issue.

The actual script is more complex than the sample I'm about to provide, but the logic behind it remains the same.

I have a business logic script that starts off with Table A. This table is the basis and contains one field (invoice amount) that needs to be overridden with the correct values- hence the creation of this BL.

To quickly visualize it for everyone, the entries for table A end up looking like this:

4589762 1 E 500.05 75635 01 2075624 1000

Invoice Amt will be renamed to [Invoice Amt_Past] to easily differentiate it from the new Invoice Amt that will be generated as the script progresses.

The initial primary keys - to allow the succeeding tables to link back to Table A - are also generated here.
[Requision Doc] & '/' & [Requisition Item] as %RecDoc%RecItem

[RefID] & '/' & [RefLev] as %RefID%RefItem

[OrderID] & '/' & [OrderLev] as %OrderID%OrderLev

The new invoice amount will come from two different tables (Table B & Table C), depending on the category it has been assigned in Table A. The new invoice amount for Category 'E' documents will be pulled from Table B, and the new invoice amount for documents with Category 'S' will be taken from Table C.

This wouldn't be an issue, but the category field is currently not being generated in our base table, which makes it unavailable to use. So we had to work around this by saying "if there's an amount in Table B for this document, take this, otherwise, take the amount in Table C".

Which worked fine up until a few days ago, which we'll get to in a little bit.

For table B, the entries will look like this:

RefID | RefLev | Amount_Ref

75635 01 453.12

We create the key %RefID%RefItem, grab the new amount (Amount_Ref), and left join it to Table A.

Getting to Table C requires an additional table in between (Table C5), because we will need to link Table C to Table C5 in order to get it connected back to Table A.
So we start off with Table C5:

OrderID | OrderLev | EventID | EventLev

2075624 1000 888456 00100

2075624 1000 888456 00200

The following keys are then created: %OrderID%OrderLev and %EventID%EventLev

Table C:

EventID | EventLev | Amount_Event

888456 00100 0
888456 00200 453.12

Key %EventID%EventLev is created and left joined to Table C5.

A third table is then generated, resident loading [%OrderID%OrderLev] and Amount_Event from C5, which we then left join back to Table A.

But because of the two lines that exist in Table C5, when the last left join happens, my Amount_Ref cost DUPLICATES. Doubling when it really shouldn't. Due to the table now looking like this:

4589762 1 75635 01 2075624 1000 453.12

Rather than the total just being 453.12, it's now 906.24. The left join is necessary because at the end of the script, all of the amounts gets consolidated into 1 table, generating just a single field: Amount_Final that we then reference in the data model later on for master measurers and visualizations.

I've sent a request in to get that category added to the raw script, but until we can get that included, I unfortunately need to work around the system. Does anyone have any suggestions as to how I can eradicate the duplicate line? Loading distinct and even removing the SUM and aggregations inside the code itself did not work 😞

Re: Duplicate lines doubling amount in script due to left join

Bhushan_Mahajan — Thu, 27 Mar 2025 05:44:59 GMT

@bvendeza Mathematically You can take Average over sum so even if multiple records are there you will get the right sum since your amount value is same for multiple entries.

Re: Duplicate lines doubling amount in script due to left join

marcus_sommer — Thu, 27 Mar 2025 07:37:05 GMT

I wouldn't tend to join these tables else to map them. It has no risks to change the number of records and the mapped value could be immediate evaluated instead needing an afterward load-step. Further mappings could be nested in various ways, for example within their third parameter might the next mapping be called.

topic Re: Duplicate lines doubling amount in script due to left join in App Development

Duplicate lines doubling amount in script due to left join

Re: Duplicate lines doubling amount in script due to left join

Re: Duplicate lines doubling amount in script due to left join