
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
IBAN checksum calculating
Hi, I need to make Qlik Sense App which will return if given word is or is not a valid IBAN.
I made this app in C but I was asked to do it in Qlik Sense and in Qlik I am an amateur.
My loaded words looks like this: SK6281009029810000571856
I need to store it in some string I think... Then the first 4 characters go to the end 1.pos -> 25.pos etc. and all letters are changed to numbers like this (A = 10; B = 11....Z = 35)
This number I need to devide by MOD operation but Qlik doesnt take that large number so I have another algorithm.
First 9 digits are devided by 97 and remainder is written down. Then after the remainder is added 7 or 8 another digits from IBAN (if remainder < 10, added 8 digits; if remainder >= 10, added 7 digits) and devided by MOD 97 and so on until all digits from iban arent used. If 1 is returned then IBAN is valid.
Example:
IBAN = SK6281009029810000571856
IBAN changed = 81009029810000571856282062 (S = 28, K = 20)
First 9 digits MOD 97: 810090298 MOD 97 = 36
Remainder + 7 digits: 361000057 MOD 97 = 7
Remainder + 8 digits: 718562820 MOD 97 = 12
Remainder + last IBAN changed digits: 1262 MOD 97 = 1 (valid IBAN)
But how can I do it in Qlik ?
Thanks for any help.
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Here you are... the script is broken into pieces... see if you can understand it now
LetterMapping: Mapping LOAD Chr(IterNo() + 64) as Letter, IterNo() + 9 as Number AutoGenerate 1 While IterNo() + 64 <= 90; Table: LOAD IBAN, IBAN_Changed, Step1, Step1_Balance, Step2, Step2_Balance, Mod(Step2, 97) & Step2_Balance as Step3, Mod(Mod(Step2, 97) & Step2_Balance, 97) as IBAN_Validation_Number; LOAD IBAN, IBAN_Changed, Step1, Step1_Balance, Mod(Step1, 97) & If(Mod(Step1, 97) > 10, Left(Step1_Balance, 7), Left(Step1_Balance, 8)) as Step2, Text(If(Mod(Step1, 97) > 10, Mid(Step1_Balance, 8), Mid(Step1_Balance, 9))) as Step2_Balance; LOAD IBAN, IBAN_Changed, Mod(Left(IBAN_Changed, 9), 97) & If(Mod(Left(IBAN_Changed, 9), 97) > 10, Mid(IBAN_Changed, 10, 7), Mid(IBAN_Changed, 10, 8)) as Step1, Text(If(Mod(Left(IBAN_Changed, 9), 97) > 10, Mid(IBAN_Changed, 17), Mid(IBAN_Changed, 18))) as Step1_Balance; LOAD IBAN, MapSubString('LetterMapping', IBAN1) as IBAN_Changed; LOAD IBAN, Mid(IBAN, 5) & Left(IBAN, 4) as IBAN1; LOAD * INLINE [ IBAN SK6281009029810000571856 SK8984202690187920591767 SK2281300957112591852596 SK7107200529879258719576 SK3865007928712659091859 SK5779307295879276189520 SK3552002689628928576817 SK9483602579810927560176 SK2430000287689856817671 SK0656001928575619095267 SK8281802890957529871762 ];

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Try like this
LetterMapping:
Mapping
LOAD Chr(IterNo() + 64) as Letter,
IterNo() + 9 as Number
AutoGenerate 1
While IterNo() + 64 <= 90;
Table:
LOAD *,
Mod(Mod(Mod(Mod(First9IBAN, 97) & Next7IBAN, 97) & Next8IBAN, 97) & LastIBAN, 97) as IBAN_Validation_Number;
LOAD IBAN,
IBAN_Changed,
Left(IBAN_Changed, 9) as First9IBAN,
Mid(IBAN_Changed, 10, 7) as Next7IBAN,
Mid(IBAN_Changed, 17, 😎 as Next8IBAN,
Mid(IBAN_Changed, 25) as LastIBAN;
LOAD IBAN,
MapSubString('LetterMapping', IBAN1) as IBAN_Changed;
LOAD IBAN,
Mid(IBAN, 5) & Left(IBAN, 4) as IBAN1;
LOAD * INLINE [
IBAN
SK6281009029810000571856
];

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thanks, it works. But only for that one IBAN number.
The remainder of first modulo isnt always 2 digits and remainder of second modulo isnt always 1 digit.
I need to implement this: if remainder < 10, added 8 digits; if remainder >= 10, added 7 digits
And if I may ask you...I dont fully understand that script, could you please try to explain at least in few sentences ?
Thanks a lot.
Here are some other IBANs I need to check:
SK8984202690187920591767
SK2281300957112591852596
SK7107200529879258719576
SK3865007928712659091859
SK5779307295879276189520
SK3552002689628928576817
SK9483602579810927560176
SK2430000287689856817671
SK0656001928575619095267
SK8281802890957529871762

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Here you are... the script is broken into pieces... see if you can understand it now
LetterMapping: Mapping LOAD Chr(IterNo() + 64) as Letter, IterNo() + 9 as Number AutoGenerate 1 While IterNo() + 64 <= 90; Table: LOAD IBAN, IBAN_Changed, Step1, Step1_Balance, Step2, Step2_Balance, Mod(Step2, 97) & Step2_Balance as Step3, Mod(Mod(Step2, 97) & Step2_Balance, 97) as IBAN_Validation_Number; LOAD IBAN, IBAN_Changed, Step1, Step1_Balance, Mod(Step1, 97) & If(Mod(Step1, 97) > 10, Left(Step1_Balance, 7), Left(Step1_Balance, 8)) as Step2, Text(If(Mod(Step1, 97) > 10, Mid(Step1_Balance, 8), Mid(Step1_Balance, 9))) as Step2_Balance; LOAD IBAN, IBAN_Changed, Mod(Left(IBAN_Changed, 9), 97) & If(Mod(Left(IBAN_Changed, 9), 97) > 10, Mid(IBAN_Changed, 10, 7), Mid(IBAN_Changed, 10, 8)) as Step1, Text(If(Mod(Left(IBAN_Changed, 9), 97) > 10, Mid(IBAN_Changed, 17), Mid(IBAN_Changed, 18))) as Step1_Balance; LOAD IBAN, MapSubString('LetterMapping', IBAN1) as IBAN_Changed; LOAD IBAN, Mid(IBAN, 5) & Left(IBAN, 4) as IBAN1; LOAD * INLINE [ IBAN SK6281009029810000571856 SK8984202690187920591767 SK2281300957112591852596 SK7107200529879258719576 SK3865007928712659091859 SK5779307295879276189520 SK3552002689628928576817 SK9483602579810927560176 SK2430000287689856817671 SK0656001928575619095267 SK8281802890957529871762 ];

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
thanks for that script. I understood it but not fully. Could you please explain what exactly this part of script does ? (I think its that part when letters are becoimg numbers but i can't understand how)
LetterMapping:
Mapping
LOAD Chr(IterNo() + 64) as Letter,
IterNo() + 9 as Number
AutoGenerate 1
While IterNo() + 64 <= 90;
Thanks.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
That is exactly right... Basically, I am Chr(64) = 'A', Chr(65) = 'B', .... and so on... so instead of typing all the Letters with there mapping, I created a loop to do this

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
What does iterNo() do?
What does AutoGemerate 1 do ?
Why is first load as Letter and second as Number ?
I am sorry for bothering you but with my search i cannot find any proper answer to my questions:(

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Oh so you want to know how the while loop works... here you can read about it
