IBAN checksum calculating

[miro_kopecky]

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.

[sunny_talwar]

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
];

[miro_kopecky]

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

[sunny_talwar · Accepted solution]

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
];

[miro_kopecky]

Hello again,
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.

[sunny_talwar]

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

[miro_kopecky]

Okay, but what exactly is happening inside the function ?
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:(

[sunny_talwar]

Oh so you want to know how the while loop works... here you can read about it

Iterno() working with while