topic Re: IBAN-Check (Java script to QV-load script) in QlikView

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

cdss-developer — Sat, 16 Nov 2024 20:20:20 GMT

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

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

hector — Mon, 05 Aug 2019 21:16:49 GMT

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

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

Brett_Bleess — Thu, 22 Aug 2019 16:17:20 GMT

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

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

cdss-developer — Thu, 05 Sep 2019 12:10:23 GMT

Wow this great, thank you very much! 😀

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