Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Change actual field values to dummy field values

I hv a field called User Name with values of different lengths and formats as shown below

User Name

User1

ABC User

DEF

User XYZ 123

There is a requirement to create a masked field such that when i pull User Name field into the dashboard, the values should be displayed as below

User Name

XXXX

XXX XXXX

XXX

XXXX XXX XXX

i.e all the alphanumeric characters in each row of user name field should be replaced to 'X'

Is there a way to achieve this in qlikview?

1 Solution

Accepted Solutions
tresesco
MVP
MVP

You have to apply a sort order in the concat(), may be like:

t1:

Load *,

       RowNo() as Seq,

       SubField([User Name], ' ') as Separated

  Inline [

User Name

User1

ABC User

DEF

User XYZ 123

A

];

t2:

Load

       [User Name],

       Concat(Xs, ' ', Seq) as Masked

Group By [User Name];

Load

       *,

       Repeat('X',len(Separated)) as Xs

Resident t1;

DROP table t1;

Capture.PNG

View solution in original post

9 Replies
tresesco
MVP
MVP

PFA

Capture.PNG

Load

       [User Name],

       Concat(Xs, ' ') as Masked

Group By [User Name];

Load

       *,

       Repeat('X',len(Separated)) as Xs;

Load *,

       SubField([User Name], ' ') as Separated

       Inline [

User Name

User1

ABC User

DEF

User XYZ 123]

ahaahaaha
Partner - Master
Partner - Master

Hi,

May be like as attached file (as variant UI)

marcus_malinow
Partner - Specialist III
Partner - Specialist III

Alternatively

AlphaNumMaskMap:

MAPPING LOAD * INLINE

[From, To

A, X

B, X

C, X

D, X

...etc etc etc

];

Then use

MapSubstring ('AlphaNumMaskMap', Upper([User Name])) as [Masked User Name]

tresesco
MVP
MVP

Could you try if the space handling is also doable in UI?

Not applicable
Author

When I tried this with  my field, noticed that after applying subfield on the column, the User Name fields get duplicated.

For ex, if Username as only A, then after applying sub field, it looks like below

User Name     Separated

A                     A

A                    A

Also, after concatenation, the names with more than 1 word are not concatenated in the same order as the original field name.

Example: User XYZ 123 is masked as XXX XXX XXXX. However, it should be XXXX XXX XXX

Please let me know what modifications to make. I tried to take distinct values from separated field. That solved the duplication issue. However, the concatenation in original name order is still an open issue. Is there some way to add an ID to each sub field value?

Not applicable
Author

Thanks for ur reply. However, need spacing between names

Not applicable
Author

Thank you for your reply. Perhaps I can try this if the other script solution doesn't work. I will have to create a mapping list for all the special characters, numbers and alphabets.

tresesco
MVP
MVP

You have to apply a sort order in the concat(), may be like:

t1:

Load *,

       RowNo() as Seq,

       SubField([User Name], ' ') as Separated

  Inline [

User Name

User1

ABC User

DEF

User XYZ 123

A

];

t2:

Load

       [User Name],

       Concat(Xs, ' ', Seq) as Masked

Group By [User Name];

Load

       *,

       Repeat('X',len(Separated)) as Xs

Resident t1;

DROP table t1;

Capture.PNG

Not applicable
Author

Thank you. This is a very elegant approach. It worked!