Alpanumeric intervalls

Dear users,
I’m working in finance and would like to summarize single accounts from a list to balance sheet positions. As we get information from many different ERP systems (lots of affiliates) I have to deal with differents naming logics.

In table 1 I have all account ranges (numeric+alphanumeric) identifying a balance sheet position:
image

At months end I get a simple account list from each affiliate:
image

Now I need to assign the correct balance sheet position from my account ranges list. So I would like to add a third column to table 2 showing the balance sheet position.

Any suggestion?

Thanks in advance

Hi @Reinhard-Korn , welcome to the forum.

Can you please show us what that third column would look like for the sample you showed?

@Reinhard-Korn , take a look if

node works for you.

Dear bruno29a,

the outcome should be like this:
grafik

According to the range table each account is assigned to according balance sheet position.

Thanks for the info @Reinhard-Korn , and another thing to confirm, it does not really matter if they’re “account from” or “account to” correct? You only want to know what the balance sheet position is, correct?

If possible, could you upload these sample data? (csv or excel)

Also, please include in the table 1 (account ranges) at least what’s in the account list, in this example it’s missing all of them except for “300”, so it’s not a workable sample :slight_smile:

I have a solution for you, but I need proper sample data

I ended up creating some sample data so I can give you the solution. I basically do a Join between the 2 tables. So here goes.

This is how the workflow looks like:
image

Account list is the same as the sample you have:
image

For Account ranges, I had to add a few entries on top of the sample of Account ranges that you provided in order to satisfy the sample of Account list you provided:
image

And here are the results of the join:
image

And here is the workflow: Alpanumeric intervalls.knwf (8.6 KB)

Dear bruno29a,
thanks for that, but that’s not really the solution I need. Let me explain why:

The account ranges say that all accounts, e.g. beginning from ABC100 to ABC199, are summed up to position fixed assets.
So it’s like ABC100+ABC101+ABC102+ABC103…+ABC199

The reason for using ranges is that we don’t have to map each account to a balance sheet position. So using intervals is to reduce work for maintaining the mapping table. If not using intervals our mapping table would contain thousands of datasets and would be very confusing.

Many accounting systems use fixed account ranges to identify balance sheet positions. It’s a kind of speaking numbering, so that each accountant knows, when using ABC100 or ABC102 it goes to fixed assets position. Helps to reduce posting errors and also automatic checks can be better implemented.

As we have many affiliates using different accounting systems, we now have to consolidate the data at month end. So I get a lot of account balances from each company and put them in one file. Nearly every affiliate uses its own accounting system and so different accounts with different speaking numbering systems have to be matched. If all of them would be numeric I could use simple range selection in simple row filter, but I face also alphanumeric (string) intervals.

At the end the workflow should assign each dataset (or account value) in that file to a specific balance sheet position according to our mapping table.

Please find below some example data. The file contains on the first tab the account list, on the second tab the mapping table - third tab shows desired result and fourth tab shows the data flow.

Thanks in advance for your kind help.

example data.xlsx (13.1 KB)

Hi @Reinhard-Korn , sorry I misunderstood your table 1, I thought Account to and Account from where account numbers representing Debit and Credit accounts.

I have a revised solution.

This is how the workflow looks like:
image

And using the Excel sample data you provided, this is the results I get:
image

And here is the workflow: Alpanumeric intervalls.knwf (28.1 KB)

This is all calculated dynamically based on the mapping table you provided, so if you add other ranges to the table, they will automatically be included.

So, a bit of details of the logic I used. I did a split between the String part and numeric part for both the data table and the account range table. For the account range table, after splitting, I assigned from from as the min value range and the to as the max value range. They were both done via Java.

The string part is stored as “Starts with”, and in case where there was no string, this would be empty.

After that, it was just a matter of joining on “Starts with” column and then apply rule engine to filter where account was between min and max.

Enjoy

1 Like

Hi bruno29a,
I added your part to my workflow and it does exactly what I need.

Many thanks for your kind help.

1 Like

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.