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

[shekhar_analyti]

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

[shekhar_analyti]

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

[sunny_talwar]

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

Table:

LOAD *,

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

LOAD *,

    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

[..\..\Downloads\credit number twik.csv]

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

[shekhar_analyti]

Thank you Sunny Bhai ,

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

[sunny_talwar]

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?

[shekhar_analyti]

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 .

[sunny_talwar]

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

Table:

LOAD *,

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

LOAD *,

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

LOAD *,

    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

[..\..\Downloads\credit number twik.csv]

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

[sunny_talwar]

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

[shekhar_analyti]

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

[marcus_sommer]

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:

Variable That Acts Like a User-Defined Function.

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

[Peter_Cammaert]

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...