Scramble only part of a field

[#1]

I need to scramble part of a field to anonymise it. However, the prefix and suffix to the data to be scrambled both need to remain unchanged.

Just to make this really interesting, the field is a key field across many tables.

For example I need to anonymise the following:

AB01234560001

AB01234560002

The first two characters (AB) need to remain unscrambled, as do the last four (0001,0002) which are serials. Only the ID in the middle (0123456) needs to be scrambled.

[matt_crowther · Accepted solution]

This on the face of it sounds relatively simple however to practically implement it across a key field may be a little trickier than appearences suggest.

Firstly; to completely 'scramble' the entire string I'd suggest simply placing an autonumber() function around each instance on your various tables - this will also reduce the size the field takes up in your .qvw. As mentioned this will scramble the entire string therefore to keep the left and right components I'd look to use left() and right() functions to split out the strings into seperate columns - due to Qlikview's Column Compression this shouldn't have too egative an impact on your model.

Alternatively you could look to manually encrypt the central string under a 'Hidden Script' tab again split out the left and right components along with using something like mid(text,3,7) to get the section to scramble you can then apply an algorithm to that component to essentailly scramble it:

Data_TMP:

Load: left(Text,2) as First_Section,

     mid(Text,3,7)*2+468112-48741+56-78 as Mid_Section,

     right(Text,4) as Last_Section

From Data;

Data:

Load First_Section&Mid_Section&Last_Section as Semi_Encrypted_Key

Resident Data_TMP;

Drop Table Data_TMP;

Simply repeat the algorithm to maintain your key throughout your model.

Hope one of those solutions helps,

Matt - Visual Analytics Ltd

[#3]

Thanks Matt, you're a legend.

[IAMDV]

Thanks Matt. I have learnt something new today.

Thanks to russellangus and Matt.

Cheers - DV

[#5]

I have thought of an alternative approach to this. By using a custom function, you can fully control which parts of the string are scrambled and how.

[IAMDV]

Hi,

Please can you post the sample QVW document?

Thanks again for sharing.

Cheers - DV