Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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;
PFA
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]
Hi,
May be like as attached file (as variant UI)
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]
Could you try if the space handling is also doable in UI?
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?
Thanks for ur reply. However, need spacing between names
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.
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;
Thank you. This is a very elegant approach. It worked!