Matching in Script, based on count()

[americanetsMD]

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   | 12345200

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   | 12345200   | Partner bank is clear

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. 🙂

[marcus_sommer · Accepted solution]

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:

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
where len(trim(CreditorNr)) * len(trim(BankNr))
Group by CreditorNr, BankNr;

[americanetsMD · Accepted solution]

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. 🙂