20 Replies Latest reply: Jun 15, 2018 9:29 AM by Peter Cammaert

# how to create a  transformation subroutine only for one column of table for security purpose ?

Hi All ,

I have requirement in two parts :

a) How to create a  transformation subroutine only for one column of table for security purpose for first 5 numbers/letters ?
b) Transformation logic changes day wise .

 first_name last_name CREDIT CARD Nbr Date_of_Use Kerrill Stenning switch 4903490391711560 22-01-2017

So credit card number should be twiked to something like this

First number from 4903490391711560 is

Logic based on Day 7th since 22/02/2017

4 + 7 (because 22 jan 2017 was 7th day of that week) = 11

9 + 1 (1st month of year) = 10
0 + (11+10) = 21
3  * 16 (Total length of Nbr) = 48

4 * 5 ( even 5 number is even then multiply by next upper odd number , if odd then add 6) = 20

Hence

4903490391711560 changes to ---------> 111021482090391711560

Logic based on Day 6th

4 + (6*2)  (because 21 jan 2017 was 6th day of that week) = 16

9 + 1 (1st month of year) = 10
0 + (11+10) = 21
3  * 16 (Total length of Nbr) = 48

4 * 5 ( even 5 number is even then multiply by next upper odd number , if odd then add 6) = 20

4903490391711560 changes to ---------> 161021482090391711560

Similarly there are different logic for all seven days of the week @

Thanks & Regards

Shekar

• ###### Re: how to create a  transformation subroutine only for one column of table for security purpose ?

A few introductory questions:

• What is the role of date 22/2/2017 in this story? See bold black line.
• Which date is driving the algorithm selection? Today() ? The day of the current reload? Some other date?
• You describe 2 algorithms that only differ in the way they translate the first digit. Where are the 5 others?

Peter

• ###### Re: how to create a  transformation subroutine only for one column of table for security purpose ?

Hi Peter ,

Thanks for reply .

Date 22/2/2017 refers to date of swipe of credit card for user . And actually it drives every thing of algorithm .

If the date of use falls on first day of week i.e Monday then there is a set of logic , if its on 2nd day of week i.e Tuesday then there is different logic . It is just that i have highlighted logic for 7th day because 22 jan 2017 was sunday .

Other 5 algorithm i will be taking care , after going through solution for above two days

• ###### Re: how to create a  transformation subroutine only for one column of table for security purpose ?

Logic based on Day 6th

4 + (6*2)  (because 21 jan 2017 was 6th day of that week) = 16      //Why multiplying  by 2??

9 + 1 (1st month of year) = 10
0 + (11+10) = 21                                                                         //If above is correct why not 16 +10???
3  * 16 (Total length of Nbr) = 48

4 * 5 ( even 5 number is even then multiply by next upper odd number , if odd then add 6) = 20

Im not getting your logic behind this!

• ###### Re: how to create a  transformation subroutine only for one column of table for security purpose ?

4 + (6*2)  (because 21 jan 2017 was 6th day of that week) = 16      //Why multiplying  by 2??

4 + (6th day * 2) its a fixed number , 2 is fixed number

9 + 1 (1st month of year) = 10

0 + (11+10) = 21  .. year your are correct it should be 16+10 , agreed my mistake

• ###### Re: how to create a  transformation subroutine only for one column of table for security purpose ?

so if its the fifth day it should be multiplyed by 3 and so on?

because in Logic based on Day 7th you did not multiply by 2

• ###### Re: how to create a  transformation subroutine only for one column of table for security purpose ?

Actually multiplication by 2 is only for day 6 , not for day 7

• ###### Re: how to create a  transformation subroutine only for one column of table for security purpose ?

for 5 day the logic is different .. completely different

• ###### Re: how to create a  transformation subroutine only for one column of table for security purpose ?

then i guess we need to know the complete logic

• ###### Re: how to create a  transformation subroutine only for one column of table for security purpose ?

Below is logic for all

For 5 th Day

4 let the number be same = 4

9 + 1 (1st month of year) = 10

0 + (4+10) = 14

3  * 16 (Total length of Nbr) = 48

4 * lenth(CREDIT CARD) = 4*7 = 28

For 4 th Day

4 decrease by one and same be second dight = 4-1 = 3 -> 33 is final number

9 + 1 (1st month of year) = 10

0 + (33+10) = 43

3  * 16 (Total length of Nbr) = 48

4 * lenth(CREDIT CARD) + result of step 1st  = 4*7 = 28  = (4*7)+33 = 28+33 = 61

For 3 rd Day

4 incerase by one and same be second dight = 4+1 = 5 -> 55 is final number

9 + 1 (1st month of year) = 10

0 + (33+10) = 43

3  * 16 (Total length of Nbr) = 48

4 * lenth(CREDIT CARD) + result of step 1st  = 4*7 = 28  = (4*7)+55 = 28+55 =  83

For 2nd Day

4 if number is even then 11 else 17 ----- since 4 is even hence 11 is final number

9 + 1 (1st month of year) = 10

0 + (11+10) = 21

3  * 16 (Total length of Nbr) = 48

4 * lenth(CREDIT CARD) + result of step 1st  = 4*7 = 28  = (4*7)+11 = 28+11 =  39

For 1st Day

4 if number is even then 19 else 29 ----- since 4 is even hence 19 is final number

9 + 1 (1st month of year) = 10

0 + (19+10) = 29

3  * 16 (Total length of Nbr) = 48

4 * lenth(CREDIT CARD) + if 1st digit is even then 29 else 11   = (4*7)+ if 1st digit is even then 29 else 11  = 28+29 =  57

• ###### Re: how to create a  transformation subroutine only for one column of table for security purpose ?

The first 4 pieces can be done like this... not sure I understand the last piece...

Table:

Mid(Nbr, 3, 1) + Piece1 + Piece2 as Piece3;

Left(Nbr, 1) + Pick(WeekDayNum + 1, 7, If(Even(Left(Nbr, 1)), 19, 29), If(Even(Left(Nbr, 1)), 11, 17), (Left(Nbr, 1)+1)*10 + (Left(Nbr, 1)+1), (Left(Nbr, 1)-1)*10 + (Left(Nbr, 1)-1), Left(Nbr, 1), 12) as Piece1,

Mid(Nbr, 2, 1) + MonthNum as Piece2,

Mid(Nbr, 4, 1) * Len(Nbr) as Piece4;

LOAD RecNo() as RowNum,

first_name,

last_name,

CC,

Nbr,

Date_of_Use,

WeekDay(Date_of_Use) as WeekDay,

Num(WeekDay(Date_of_Use)) as WeekDayNum,

Num(Month(Date_of_Use)) as MonthNum

FROM

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

• ###### Re: how to create a  transformation subroutine only for one column of table for security purpose ?

Thank you Sunny Bhai ,

Actually needed as transformation for that column using subroutine (Which is Must needed out of all this )

• ###### Re: how to create a  transformation subroutine only for one column of table for security purpose ?

I have not created the subroutine, so not really sure how it is done... but if you know how to create it... you should be able to use the above logic, right?

• ###### Re: how to create a  transformation subroutine only for one column of table for security purpose ?

Never worked in sub routine and I am still not sure about how to put your solution into single column level

SUB ROUTINE  . Actually idea behind putting subroutine was that the logic/algorithm might change frequently after UAT , so BA wants to keep it at sub routine level .

Like all the times , thank you Sir .

• ###### Re: how to create a  transformation subroutine only for one column of table for security purpose ?

Besides the 5th piece which I don't understand... putting it together is as simple as this

Table:

Piece1 &Piece2&Piece3&Piece4&Piece5&Mid(Nbr, 6) as Required;

Mid(Nbr, 3, 1) + Piece1 + Piece2 as Piece3;

Left(Nbr, 1) + Pick(WeekDayNum + 1, 7, If(Even(Left(Nbr, 1)), 19, 29), If(Even(Left(Nbr, 1)), 11, 17), (Left(Nbr, 1)+1)*10 + (Left(Nbr, 1)+1), (Left(Nbr, 1)-1)*10 + (Left(Nbr, 1)-1), Left(Nbr, 1), 12) as Piece1,

Mid(Nbr, 2, 1) + MonthNum as Piece2,

Mid(Nbr, 4, 1) * Len(Nbr) as Piece4;

LOAD RecNo() as RowNum,

first_name,

last_name,

CC,

Nbr,

Date_of_Use,

WeekDay(Date_of_Use) as WeekDay,

Num(WeekDay(Date_of_Use)) as WeekDayNum,

Num(Month(Date_of_Use)) as MonthNum

FROM

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

• ###### Re: how to create a  transformation subroutine only for one column of table for security purpose ?

Again, I have no idea about sub-routines... but may be marcus_sommer or rwunderlich can help you with that

• ###### Re: how to create a  transformation subroutine only for one column of table for security purpose ?

Its one of the rare occasion where Sunny Bhai have to take help , Something i can now boast about

• ###### Re: how to create a  transformation subroutine only for one column of table for security purpose ?

I agree with Sunny that a sub-routine is neither necessary nor sensible else just using the native script-capabilities from Qlik.

To keep the logic maintainable and to control the from the outside you could use an include-variable (The \$(Include) which you \$(Must_Include) into your toolkit) and there each single-logic is covered through a custom-function created with parametrized variables:

Dynamic Field Expressions with Variables

Variables with Parameters

Further you could use these logic/variables within UI to decode your encoding again - if this isn't the aim behind it the whole thing makes not much sense because then it would be much easier just to hash the number and maybe the date/today or whatever.

- Marcus

• ###### Re: how to create a  transformation subroutine only for one column of table for security purpose ?

Subroutines cannot be used to do that because they do not return values. A SUBroutine can only be CALLed and CALL is a control statement which cannot be embedded in another LOAD statement.

For translating a set of values (like part of a credit card number, the date of last use, etc.) into a single transmogrified string that is then stored together with the remainder of the credit card number in a column, you'll need a function. Functions do accept multiple parameters and they do return values (or to put it differently, they must return values) and functions can be created in the QlikView Desktop Macro editor in either VBScript of in JavaScript (for the brave ). I'll see if I can find an example in the rich resource library.

Oh and BTW, it is my experience that Sunny never runs out of ideas or inspiration. Hey may run out of juice however, and in that case he just needs fresh batteries...