Issues with values not matching even though identical when doing data cleansing

[ngrayfc]

Hi all,

Been working lately on a use case which requires data cleansing for an export:
All external adjustments as a summary in a CSV export - where if there’s multiple adjustments of the same account code it compiles those ones together. Right now I’m trying to get the cleansing right in a Reconciliation where I can see the values and behaviour clearer.

I’ve tried several ways to approach so far…
I tried to do matching the way Silverfin recommends (as per the people example where gender is male or female) but because I need to go from adjustments side and the account code value is multiple levels down it has made it more convoluted.

Right now I’m doing the following - This is very manual and visual leaning because I had some weird behaviour when I was trying to cleanse where I have two account codes which are identical but Silverfin won’t/can’t match them for unknown reasons:

{% comment %} creating empty arrays to shuffle data about {% endcomment %}
{% assign adjustmentList = '' | split: '|' %}  {% comment %} finally array we use for final table {% endcomment %}
{% assign existingAccountCodes = '' | split:'|' %} {% comment %} array we use to out all the account codes {% endcomment %}
{% assign transactionListToCleanse = '' | split: '|' %} {% comment %} array we use to dump all the transactions {% endcomment %}
{% assign thisTransactionToCleanse = '' | split: '|' %} {% comment %} array we use to dump all the transactions for the current transaction we want to cleanse  {% endcomment %}

{% for adjustment in period.adjustments.external %} {% comment %} loop through all the external adjustments in the period {% endcomment %}
  {% assign adjustmentTransaction = adjustment.transactions.first %} {% comment %} we only need to get one transaction per each adjustment since we know it will be for the same account code at this level {% end comment %}
  {% push adjustmentTransaction.account.original_number to:existingAccountCodes %} {% comment %} put the account code of each into account code list {% endcomment %}
  {% push adjustmentTransaction to:transactionListToCleanse %} {% comment %} Also take the entire transaction {% endcomment %}
{% endfor %}

{% comment %} capture the unique account codes{% endcomment %}
{% capture uniqueAccountCodes %} 
  {{ existingAccountCodes | uniq | join:',' }}
{% endcapture %}

{% comment %} convert the above capture into an array we can loop through {% endcomment %}
{% assign finalAccountCodes = uniqueAccountCodes | split:',' %}

{% comment %} for each account code we then want to go through every transaction and first of all check for matches so we can continue the logic later {% endcomment %}
{% for account in finalAccountCodes %} 
{% comment %} this is a work around which realistically should be used {% endcomment %}
 {% if account ==  finalAccountCodes.first%}
    {% assign theFirstTransaction = transactionListToCleanse.first %}
    {% push theFirstTransaction to:adjustmentList %}
 {% endif %}

{% comment %} this goes through every account code and checks the transactions account codes match. This is where my issue is {% endcomment %}
  {% for transaction in transactionListToCleanse %}
    {% if account == finalAccountCodes.first %}
    {% else %}
      {%capture transactionOriginal %}{{ transaction.account.original_number }}{% endcapture %}
      {{ transactionOriginal }} - {{ account }} - {% if transactionOriginal contains account  %}Yes{% endif %}
    {% endif %}
  {% endfor %}
{% endfor %}

The way I am trying to do this feels so gross I am certain there must be a better way which yields better results. The way I am doing does not even guarantee a match since for some reason Silverfin thinks the first and last values in the array(s) are not a match even if the values are identical (I know this due to visual debugging I’ve done which is accounted for in the code above - I have tried several operators: contains, ==, !=, etc - all yield the same issue).

I tried to approach it from a debugging perspective with the following assumptions -

1. That Silverfin could be treating them as objects and since they are not from the same adjustment transaction so it won’t match
2. That it perceives one as a string and one as an int

Regardless if I try to force cast the values or use different operators, Silverfin can’t match the first and last values in the array(s). I tried to get around this in the code above by auto pushing the first and last values but I can’t always guarantee that the first and last values will be a match. So this is logically redundant too.

Example - If you comment out the code which checks if we are looking at the first Account code value, then the first value in my demo code shows this (the yes means that Silverfin thinks it is a match):

240301.000 - 240301.000 -
140502.000 - 240301.000 -
140301.000 - 240301.000 -
141200.000 - 240301.000 -

You can clearly see that the first two are identical, but for some reason they won’t match. In the next set of values I get this:

240301.000 - 140502.000 -
140502.000 - 140502.000 - Yes
140301.000 - 140502.000 -
141200.000 - 140502.000 -

Which demonstrates It can match and it continues to work until it gets to the last two in the list.

I did try to see if there was any documentation regarding filtering arrays or filtering drops a bit more precisely but couldn’t find anything that worked for my use case…

Any corrections, advice, suggestions welcome. Think I’ve hit a bit of a knowledge cap with this one

[Romy_Vermeeren]

Hi @ngrayfc

I can see where the struggles are coming from, as unfortunately the adjustments are not available on the accounts drop and we need to match them manually.

If I understand your use-case correctly, you want to

1. Generate a list of all the accounts where external adjustments have been booked.
2. This list should not contain doubles.

I would propose using the following code as a basis:

{% comment %}Create empty array{% endcomment %}
{% assign external_accounts_array = "" | split:";" %}

{% assign external_adjustments = period.adjustments.external | sort:'number' %}

{% for adjustment in external_adjustments %}
  {% assign transactions_external = adjustment.transactions %}
  {% for external_transaction in transactions_external %}
  
    {% comment %}Get the account number for every account{% endcomment %}
    {% assign account_number = external_transaction.account.original_number %}
    
    {% comment %}Send account number to array{% endcomment %}
    {% push account_number to:external_accounts_array %}
    
  {% endfor %}
{% endfor %}

{% comment %}Remove duplicates{% endcomment %}
{% assign external_accounts_array = external_accounts_array | uniq %}

{% comment %}Print all accounts{% endcomment %}
{% for account in external_accounts_array %}
  {{ account }}
{% endfor %}

I tested this piece of code against your original, and noted that it outputs more accounts.
The reason is that you stop the loop after the first transaction for every adjustment, however an adjustment can have several transactions that relate to different accounts.

I’d also need to point out that looping through the transactions in a reconciliation template can be a bit slow and have some impact on performance if there are a LOT of transactions, but since you’ll eventually move it to an export file I think you should be fine!

I hope I understood your use case correctly, if not please reach out or if you need more help building onto this piece of code.

Kind regards,
Romy

[ngrayfc]

I’m afraid my issue is more than just the accounts…

I can get all the unique accounts already with my code. The issue and the reason I am doing that is that I need the transactions so I can take certain fields for my export for each account and I only want the external adjustments data. I do not want anything else.

1. Original account code (how I am filtering the accounts)
2. Original account name
3. Original Value
4. External adjustments total for that period
5. Sum of Original Value and External adjustments (i.e. the new value of the account)

That’s the reason why I am going through each transaction… Since I do not see Silverfin’s database and the drops does not show the relationship between the entities too well in some cases, it is difficult to see which direction I should go from to tackle this problem.

{% assign external_adjustments = period.adjustments.external | sort:'number' %}

This piece of code could be of use to me if I can do it off the accounts original number?

Thanks for your response, I’ll have another look at my code with your example and see if I can get the values I need for the export.

[Romy_Vermeeren]

Hi @ngrayfc ,

Thank you for elaborating.
The number I’m sorting on is just the adjustment number. Unfortunately the account number is not something we can access on this drop.
I’ve taken my previous code and adjusted it based on all the information you want to fetch.
In this case I think it makes sense to push the entire account into an array, since you want to fetch more than just the number later on.
Then with a dynamic variable we can keep track of the total external adjustment posted on each account.

{% comment %}Create empty array{% endcomment %}
{% assign external_accounts_array = "" | split:";" %}

{% assign external_adjustments = period.adjustments.external | sort:'number' %}

{% for adjustment in external_adjustments %}
  {% assign transactions_external = adjustment.transactions %}
  {% for external_transaction in transactions_external %}
  
    {% comment %}Fetch account ID for use in dynamic var{% endcomment %}
    {% assign account_id = external_transaction.account.id %}
    
    {% comment %}Fetch the account and push it to the array{% endcomment %}
    {% assign adjusted_account = external_transaction.account %}
    {% push adjusted_account to:external_accounts_array %}    
    
    {% comment %}Value of each external adjustment{% endcomment %}
    {% assign adjustment_value = external_transaction.value %}
    
    {% comment %}Dynamic variable to retrieve the total external adjustment for each account{% endcomment %}
    {% capture external_adjustments_account %}external_adjustments_{{ account_id }}{% endcapture %}
    
    {% comment %}Add adjustment value to the dynamic variable for every external transaction{% endcomment %}
    {% assign [external_adjustments_account] = [external_adjustments_account]+adjustment_value %}    
    
  {% endfor %}
{% endfor %}

{% comment %}Remove duplicates{% endcomment %}
{% assign external_accounts_array = external_accounts_array | uniq %}

{% comment %}Print all accounts with external adjustments in this period{% endcomment %}
{% for account in external_accounts_array %}
  
  {% comment %}Rebuild dynamic var{% endcomment %}
  {% assign account_id = account.id %}
  {% capture external_adjustments_account %}external_adjustments_{{ account_id }}{% endcapture %}
  
  Original number: {{ account.original_number }}
  Original name: {{ account.name }}
  Original value: {{ account.value_without_adjustments | currency }}
  External adjustments total for that period {{ [external_adjustments_account] | currency }}
  Sum of Original Value and External adjustments (i.e. the new value of the account) {{ account.value_without_adjustments+[external_adjustments_account] | currency }}

{% endfor %}

I hope this was useful and you can use it as a basis !

As to how these drops relate to each other, you were definitely on the right path.
The adjustments drop can bring you to the transactions, in which you can then access the accounts drop. Which means there’s no direct link between adjustments and accounts; the whole reason we need to work our way around it.

Kind regards,
Romy

[ngrayfc]

Good morning Romy,

Will take what you’ve shown me today and adjust my code to fit
A lot of similarities in the logic between us but as expected you have a definite better way to write it (which was what I was hoping for). Will make sure to note the techniques you have used to better some of my code!

I’ll let you know if it is enough.
Yeah, my code would have worked but there’s something off about the current account number not matching consistently with the account number per each transaction.

Nevermind!

Thank you and will let you know if anything else is needed

Natasha

[ngrayfc]

Good morning Romy,

In my client this is what your code brings back:

240301.000
140502.000
140502.000
140301.000
140301.000
240000.000
141200.000
500003.000

This still brings me back duplicates, which is not what I need.

In my code - the reason I look through each account code then through every transaction to match the account code is so I could remove the duplicates above

Kind regards,
Natasha

[Romy_Vermeeren]

Good morning Natasha @ngrayfc

You’re right, the uniq filter worked with the account numbers, but apparently not on accounts.
I see the same in my test file.

I’ve added an unless statement in the code where we push the accounts into the array:

    {% comment %}Add account unless it's already in the array{% endcomment %}
    {% unless external_accounts_array contains adjusted_account %}
     {% push adjusted_account to:external_accounts_array %}    
    {% endunless %}

This solved it for me, would you mind trying if this works for you too?

Kind regards,
Romy

[ngrayfc]

Good morning Romy,

That’s what I needed… Thank you so much, this use case has been giving me a lot of grief…

Kind regards,
Natasha