Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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. 🙂
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;
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. 🙂
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;
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. 🙂