topic Re: Matching in Script, based on count() in App Development

Matching in Script, based on count()

americanetsMD — Thu, 28 Mar 2024 08:19:15 GMT

Hi everyone,

i have a small logic problem matching 2 fields based on their count amount. i have managed to get the required result, using the set analysis, but this is not working as expected when i want to filter, based on this result field.

So, consider having the following Table:

CreditorNr | Type | BankNr

010001 | 200 | 12345200

010001 | 200 | 12345100

010001 | 100 | 12345100

Using the following formula, i obtained the following MatchField:

=aggr(If(
Count(Distinct Type ) = 1 and
Count(Distinct BankNr) = 1, 'Partner bank is clear', 'Partner bank is not clear'), CreditorNr , BankNr)

i get following results, that are also correct:

CreditorNr | Type | BankNr | MatchField

010001 | 200 | 12345200 | Partner bank is clear

010001 | 200 | 12345100 | Partner bank is not clear

010001 | 100 | 12345100 | Partner bank is not clear

Anyway, when trying to implement this in the Script, i dont get the correct result. In the Script i use the following code:

TEMP:
NoConcatenate
Load CreditorNr,
If(Count(Distinct Type) = 1 and
Count(Distinct BankNr) = 1, 'Partner bank is clear', 'Partner bank is not clear') as ScriptMatch
Resident BankTab
Group by CreditorNr, BankNr;

And i get the following result:

CreditorNr | Type | BankNr | MatchField | ScriptMatch

010001 | 200 | 12345200 | Partner bank is clear | Partner bank is clear

010001 | 200 | 12345200 | Partner bank is clear | Partner bank is not clear

010001 | 200 | 12345200 | Partner bank is clear | Partner bank is clear

010001 | 200 | 12345200 | Partner bank is clear | Partner bank is not clear

010001 | 200 | 12345200 | Partner bank is clear | Partner bank is clear

010001 | 200 | 12345200 | Partner bank is clear | Partner bank is not clear

010001 | 200 | 12345100 | Partner bank is not clear | Partner bank is clear

010001 | 200 | 12345100 | Partner bank is not clear | Partner bank is not clear

010001 | 100 | 12345100 | Partner bank is not clear | Partner bank is clear

010001 | 100 | 12345100 | Partner bank is not clear | Partner bank is not clear

So it basically goes through every BankNr and duplicates the data, once bank is clear, and once bank is not clear...

Can anyone please give me a hint on what im i doing wrong in the script?

Thank you for the support. 🙂

Re: Matching in Script, based on count()

marcus_sommer — Thu, 28 Mar 2024 12:08:03 GMT

Maybe the real data-set is different to your expectation. To check this you need to use a table-box and not a chart and adding there the relevant fields + an unique ID. If none such ID exists you could create one by adding rowno() to the load.

Beside of this you could check if anything changed within your aggregation load if you add:

Re: Matching in Script, based on count()

americanetsMD — Thu, 28 Mar 2024 14:33:30 GMT

Hi Marcus,

Thank you very much for your help. I have managed to get the correct result after your suggestion with the ID's. 🙂

The code i wrote to get the desired output is:

TEMP:
Load
CreditorNr,
BankNr,
Count(DISTINCT BankNr) as BankNrCounter,
Count(DISTINCT Type) as TypeCounter
Resident FinalTab
Group by CreditorNr, BankNr;

TEMP1:
Load
CreditorNr,
BankNr,
If(Match(BankNrCounter, TypeCounter), 'Partner bank is clear', 'Partner bank is not clear') as ScriptMatch

Resident TEMP;
Drop Table TEMP;

Thanks again Marcus. 🙂