IBAN-Check (Java script to QV-load script)

[cdss-developer]

Hello,

I want to build a IBAN (Bankaccount) check script to see if the bankaccount is valid. 
I found this piece of code online. But it's java. Can anyone help me to convert this to a QlikView load script?

Thanks in advance.

Greetings,
Eelco

function alertValidIBAN(iban) {
if(iban==null){
alert(isValidIBANNumber(document.getElementById("iban").value));
}else{
alert(isValidIBANNumber(iban));
}
}
/*
* Returns 1 if the IBAN is valid
* Returns FALSE if the IBAN's length is not as should be (for CY the IBAN Should be 28 chars long starting with CY )
* Returns any other number (checksum) when the IBAN is invalid (check digits do not match)
*/
function isValidIBANNumber(input) {
var CODE_LENGTHS = {
AD: 24, AE: 23, AT: 20, AZ: 28, BA: 20, BE: 16, BG: 22, BH: 22, BR: 29,
CH: 21, CR: 21, CY: 28, CZ: 24, DE: 22, DK: 18, DO: 28, EE: 20, ES: 24,
FI: 18, FO: 18, FR: 27, GB: 22, GI: 23, GL: 18, GR: 27, GT: 28, HR: 21,
HU: 28, IE: 22, IL: 23, IS: 26, IT: 27, JO: 30, KW: 30, KZ: 20, LB: 28,
LI: 21, LT: 20, LU: 20, LV: 21, MC: 27, MD: 24, ME: 22, MK: 19, MR: 27,
MT: 31, MU: 30, NL: 18, NO: 15, PK: 24, PL: 28, PS: 29, PT: 25, QA: 29,
RO: 24, RS: 22, SA: 24, SE: 24, SI: 19, SK: 24, SM: 27, TN: 24, TR: 26
};
var iban = String(input).toUpperCase().replace(/[^A-Z0-9]/g, ''), // keep only alphanumeric characters
code = iban.match(/^([A-Z]{2})(\d{2})([A-Z\d]+)$/), // match and capture (1) the country code, (2) the check digits, and (3) the rest
digits;
// check syntax and length
if (!code || iban.length !== CODE_LENGTHS[code[1]]) {
return false;
}
// rearrange country code and check digits, and convert chars to ints
digits = (code[3] + code[1] + code[2]).replace(/[A-Z]/g, function (letter) {
return letter.charCodeAt(0) - 55;
});
// final check
return mod97(digits);
}
function mod97(string) {
var checksum = string.slice(0, 2), fragment;
for (var offset = 2; offset < string.length; offset += 7) {
fragment = String(checksum) + string.substring(offset, offset + 7);
checksum = parseInt(fragment, 10) % 97;
}
return checksum;
}

[hector · Accepted solution]

Hi, see if this scripts helps you. It doesn't have all the validations (especially the regex ones), but maybe a good aproach. I developed two versions, in QV with a macro helper and this one only with script functions so it will run in QS also.

MAP_LENGTH:
Mapping
Load * Inline[
CountryCode, Length
AD24 
AE, 23 
AT, 20 
AZ, 28 
BA, 20 
BE, 16 
BG, 22 
BH, 22 
BR, 29
CH, 21 
CR, 21 
CY, 28 
CZ, 24 
DE, 22 
DK, 18 
DO, 28 
EE, 20 
ES, 24
FI, 18 
FO, 18 
FR, 27 
GB, 22 
GI, 23 
GL, 18 
GR, 27 
GT, 28 
HR, 21
HU, 28 
IE, 22 
IL, 23 
IS, 26 
IT, 27 
JO, 30 
KW, 30 
KZ, 20 
LB, 28
LI, 21 
LT, 20 
LU, 20 
LV, 21 
MC, 27 
MD, 24 
ME, 22 
MK, 19 
MR, 27
MT, 31 
MU, 30 
NL, 18 
NO, 15 
PK, 24 
PL, 28 
PS, 29 
PT, 25 
QA, 29
RO, 24 
RS, 22 
SA, 24 
SE, 24 
SI, 19 
SK, 24 
SM, 27 
TN, 24 
TR, 26
];


Sub isValidIBANNumber(TABLE, IBAN_FIELD)

	//Copy of the original table
	[TMP_IBAN_VALIDATION_$(IBAN_FIELD)]:
	Load
		Distinct
		$(IBAN_FIELD) as [TMP_$(IBAN_FIELD)]
	resident $(TABLE);
	
	let vN_IBAN_VALIDATION = NoOfRows('TMP_IBAN_VALIDATION_$(IBAN_FIELD)');
	
	for i = 0 to vN_IBAN_VALIDATION - 1;
	
		Let vIBAN = peek('TMP_$(IBAN_FIELD)',i,'TMP_IBAN_VALIDATION_$(IBAN_FIELD)');

		Let input = keepchar(upper(vIBAN),'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ');
		Let code_01 = Left(input,2);	//Country
		Let code_02 = mid(input,3,2);	//Check digits
		Let code_03 = mid(input,5);		//Rest digits
	
		Let digits = text(
		replace(
		replace(
		replace(
		replace(
		replace(
		replace(
		replace(
		replace(
		replace(
		replace(
		replace(
		replace(
		replace(
		replace(
		replace(
		replace(
		replace(
		replace(
		replace(
		replace(
		replace(
		replace(
		replace(
		replace(
		replace(
		replace(
				code_03 & code_01 & code_02,
				'A',
				Ord('A') - 55
				),
				'B',
				Ord('B') - 55
				),
				'C',
				Ord('C') - 55
				),
				'D',
				Ord('D') - 55
				),
				'E',
				Ord('E') - 55
				),
				'F',
				Ord('F') - 55
				),
				'G',
				Ord('G') - 55
				),
				'H',
				Ord('H') - 55
				),
				'I',
				Ord('I') - 55
				),
				'J',
				Ord('J') - 55
				),
				'K',
				Ord('K') - 55
				),
				'L',
				Ord('L') - 55
				),
				'M',
				Ord('M') - 55
				),
				'N',
				Ord('N') - 55
				),
				'O',
				Ord('O') - 55
				),
				'P',
				Ord('P') - 55
				),
				'Q',
				Ord('Q') - 55
				),
				'R',
				Ord('R') - 55
				),
				'S',
				Ord('S') - 55
				),
				'T',
				Ord('T') - 55
				),
				'U',
				Ord('U') - 55
				),
				'V',
				Ord('V') - 55
				),
				'W',
				Ord('W') - 55
				),
				'X',
				Ord('X') - 55
				),
				'Y',
				Ord('Y') - 55
				),
				'Z',
				Ord('Z') - 55
		)
		);		
		
		//function mod97
		Let checksum = left(digits,2);

		for offset = 3 to len(digits) step 7
			fragment = checksum & mid(digits,offset,7)
			checksum = mod(fragment,97)
		next	
	
		[IBAN_VALIDATION_$(IBAN_FIELD)]:
		Load
			'$(vIBAN)'		as [$(IBAN_FIELD)],		//Automatically will link the original field
			//'$(digits)' 	as digits,				//Just for validation purposes
			if(applymap('MAP_LENGTH','$(code_01)',0) <> len('$(input)'),
				0,
				if($(checksum) = 1, 1, 0
				)
			) 	as isValidIBANNumber				//Flag
		autogenerate(1);
	
	
	next
	
	drop Table TMP_IBAN_VALIDATION_$(IBAN_FIELD); //no longer used table
end Sub



//Example data
FT:
LOAD
	F1
INLINE [
    F1
    SE35 5000 0000 0549 1000 0003
    SE35 5000 0000 0549 1000 000
    CH93 0076 2011 6238 5295 7
    DE89 3704 0044 0532 0130 00
    MT84 MALT 0110 0001 2345 MTLC AST0 01S
    MT84 MALT 0110 0001 2345 MTLC AST0 012
];

call isValidIBANNumber('FT','F1'); //Parameter 1:Table Name, 2: Field Name //Just in case there will be more than one field

The example data was taken from https://www.citadele.lt/en/iban-examples/

Hope it helps

Regards

[Brett_Bleess]

Eelco, did Hector's post do the trick?  If so, please be sure to use the Accept as Solution button to give him credit for the help and to let others know it worked.  If you did something else, consider letting everyone know what that was and mark that as the solution.

Regards,
Brett

[cdss-developer]

Wow this great, thank you very much! 😀

Apologies for my late reaction (was on holiday). You really helped me a lot.