topic Re: How to use PurgeChar along with string function in QlikView

How to use PurgeChar along with string function

Wed, 25 Nov 2020 16:16:04 GMT

Hello,

I am new to QlikView and i have to develop a report which gives pure data without any special char.

I do not want any blanks/any junk nor the numeric. Only characters is required.

Could anyone please help me on this.

Thank you,

Regards,

Sandhya

Re: How to use PurgeChar along with string function

sunny_talwar — Fri, 09 Jun 2017 09:36:53 GMT

May be just this

KeepChar(FieldName, 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz') as FieldName

Re: How to use PurgeChar along with string function

qlikview979 — Fri, 09 Jun 2017 09:37:22 GMT

Hi,

Please provide your sample data with expected output

Re: How to use PurgeChar along with string function

Fri, 09 Jun 2017 09:46:31 GMT

Hello Sunny,

Thanks for your response.

I tried with this syntax KeepChar(FieldName, 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz') as FieldName, but it returns the junk value which I do not require.

So I tried in other way KeepChar(FieldName, '!!!,###,***,$$$,12345,()()()')) as FieldName and think so it is working. but dont know is this the correct way of using the function.

Thank you,

Regards,

Sandhya

Re: How to use PurgeChar along with string function

Fri, 09 Jun 2017 09:49:45 GMT

Hello Mahesh,

I am initially trying this on one field (say Customer Name) and I want to have pure data without any special character

for example: Mahesh Kumar is Pure data... but ####Mahesh****!!!Kumar is data with special characters.

Hope I am able to provide you with the details of what I am looking for.

Thank you,

Regards,

Sandhya

Re: How to use PurgeChar along with string function

qlikview979 — Fri, 09 Jun 2017 15:19:03 GMT

Hi Sanghya,

Sorry for delay...

T1:

LOAD

KeepChar([Customer Name],'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz');

LOAD * Inline [

Customer Name

####Mahesh****!!!Kumar

Sandhya###Japtap&%(@&#&%

];

Result:-

MaheshKumar

SandhyaJaptap

Regards

Re: How to use PurgeChar along with string function

Tue, 13 Jun 2017 09:52:55 GMT

Hello Mahesh,

Thanks for the response. Using KeepChar function, I am able to get the result however there is one problem in using this function we are not able to get the char and number combination data.

for example: Amit1234 is a pure data but how to get this using keepchar or purge char is a problem

Could you be able to help in this.

Thank you,

Regards,

Sandhya

Re: How to use PurgeChar along with string function

qlikview979 — Tue, 13 Jun 2017 16:26:25 GMT

Hi sandhya,

keepchar(Amit1234,'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz') //

it will give result Amit

purgechar(Amit1234,'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz')

it will give result 1234

regards

mahesh

Re: How to use PurgeChar along with string function

Thu, 15 Jun 2017 08:23:28 GMT

hello mahesh,

Thanks for your response. I am still not getting the correct answer. below is my data

out of which I want pure data for example ('Dustin Graves', 'Ashish','Sandhya',Amit1234') only. the other data including special character and only numbers should not be considered. Using PurgeChar and KeepChar, I am not able to get the combination of char and numbers. Could you be able to help here how to get this done.

Thank you,

Regards,

Sandhya

123Art Johnson,III

Dustin Graves

!!!$%VALMONT INDUSTRIES, INC.

PACCAR####

CTC-Suite Renovation Program-1x4

&&&*****Unknown****

****CRM****####

12345 JCI project. 57 Luxor 300 156 Luxor 600

AAP !!!####%%%%&&&

****AAMCOR ***

123134343424542sasdsfpoi*******

Ashish

423165

Amit1234

Re: How to use PurgeChar along with string function

qlikview979 — Thu, 15 Jun 2017 09:40:42 GMT

Hi Sandhya,

can you please share the Input and Required output,that is easy for me in excel.

Re: How to use PurgeChar along with string function

qlikview979 — Thu, 15 Jun 2017 09:49:27 GMT

Hi sandhya,

are you expecting like this

Re: How to use PurgeChar along with string function

Thu, 15 Jun 2017 09:57:15 GMT

Hello Mahesh,

yes that's correct! I am expecting exactly the same. However it would be for all the data coming from source so not just few records. I want to have pure data without having any special characters, only numbers and any kind of junks like whitespaces, nulls. Just pure data and combination of char+number would do.

exactly like this, could you be able to share the code with me please that would be really helpful.

Thank you,

Regards,

Sandhya

Re: How to use PurgeChar along with string function

qlikview979 — Thu, 15 Jun 2017 15:59:42 GMT

can you please share the sample source in excel.

Re: How to use PurgeChar along with string function

qlikview979 — Fri, 16 Jun 2017 09:43:04 GMT

Hi sandhya,

Please follow this link

Text& numeric Data

Regards

Mahesh

Re: How to use PurgeChar along with string function

Tue, 20 Jun 2017 06:45:26 GMT

Hello Mahesh,

Many thanks for all the help and quick responses. I have used the same solution in my code and it is working.

Thank you once again.

Regards,

Sandhya