topic suggest in QlikView

suggest

satishqlik — Wed, 25 Nov 2020 16:16:04 GMT

Hi Community,

I have a string 01#345@78$

How to change the special characters (#,@,$) to 2,6,9 respectvely from the string?

I tried with using replace function but didn't work properly

Any ideas?

Re: suggest

MindaugasBacius — Thu, 26 May 2016 09:54:43 GMT

You can use Replace or MapSubstring functions.

Re: suggest

MindaugasBacius — Thu, 26 May 2016 10:01:36 GMT

=replace(replace(Replace('01#345@78$', '#', 2), '@', 6), '$', 9)

Well Replace works fine.

Result:

Re: suggest

satishqlik — Thu, 26 May 2016 10:14:29 GMT

Thanks but how can we do by using MapSubstring function?

It showing only two parameters map_id and text.

Re: suggest

sunny_talwar — Thu, 26 May 2016 10:17:37 GMT

May be like this:

MappingTable:

Mapping

LOAD * Inline [

From, To

#, 2

@, 6

$, 9

];

FactTable:

LOAD FieldName,

MapSubString('MappingTable', FieldName) as NewFieldName

FROM Source;

Re: suggest

tamilarasu — Thu, 26 May 2016 10:19:12 GMT

Hi Satish,

Hope you are asking for dynamic expression. If so, try below expression,

=Replace(

Replace(

Replace('01#345@78$',

'#',Mid('01#345@78$',Index('01#345@78$','#')-1,1)+1),

'@',Mid('01#345@78$',Index('01#345@78$','@')-1,1)+1),

'$',Mid('01#345@78$',Index('01#345@78$','$')-1,1)+1)

Re: suggest

MindaugasBacius — Thu, 26 May 2016 10:21:27 GMT

map1:
mapping load * inline [
     #, 2
     @, 6
     $,9
] ;


Data:
LOAD *,
MapSubstring ('map1', F1) as new
INLINE [
    F1
    01#345@78$
];

Result:

See the attachement.

Re: suggest

qlikview979 — Thu, 26 May 2016 10:25:38 GMT

Hi Satish,

You can try this

Re: suggest

satishqlik — Thu, 26 May 2016 10:26:19 GMT

Thanks for all your ideas...

Re: suggest

Saravanan_Desingh — Fri, 27 May 2016 01:46:50 GMT

One more version..

Load  Concat(Out,'') As text2;
Load *,
Alt(Mid(text,IterNo(),1)*1,IterNo()-1) As Out
Inline [
text
01#345@78$
]
While IterNo() <= Len(text);

text2 will have 0123456789