topic Re: PurgeChar() in QlikView

How to remove required characters from field value

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

Hi All,

LOAD

[Product],[Product Type],[Product Number],[Order],[Order Type],[Order Number] as Field

resindent table;

Here i need to remove "[Product Type],[Order Type]" from filed.

I tried purgechare, it removes [Product Type],[Order Type] and all [] and Product(i mean Product, Product from Product Number) from field as well.

Can you assist on this please.

Thanks,

Nihhal.

Re: PurgeChar()

Frank_Hartmann — Wed, 30 Nov 2016 08:18:31 GMT

what about omitting these fields in the load?

LOAD

[Product],[Product Number],[Order],[Order Number] as Field

resindent table;

Re: PurgeChar()

its_anandrjs — Wed, 30 Nov 2016 08:31:13 GMT

Please elaborate why you use PurgeChar()

LOAD

[Product],[Product Type],[Product Number],[Order],[Order Type],[Order Number] as Field

resindent table;

Is this your field

[Product]&[Product Type]&[Product Number]&[Order]&[Order Type]&[Order Number] as Field

Regards
Anand

Re: PurgeChar()

Anonymous — Wed, 30 Nov 2016 08:34:13 GMT

Hi,

You can use pick and match function.

Thanks,

Re: PurgeChar()

sunny_talwar — Wed, 30 Nov 2016 08:34:14 GMT

Would you be able to share few rows of data to show what you have and what you are looking to get?

Re: PurgeChar()

nihhalmca — Wed, 30 Nov 2016 08:37:45 GMT

Simple Sunny.

My direct question is, i want to exclude some chartacters from the field value however those are repeated in fied value.

I hope i explained very clear.

Re: PurgeChar()

its_anandrjs — Wed, 30 Nov 2016 08:41:37 GMT

Please share some data to have a look.

Regards

Anand

Re: PurgeChar()

nihhalmca — Wed, 30 Nov 2016 08:43:35 GMT

Hi Anand,

I shared field "value" in my first post (question).

Re: PurgeChar()

sunny_talwar — Wed, 30 Nov 2016 08:50:12 GMT

May be use Replace multiple times

LOAD *,

Replace(Replace(Field, [Product Type], ''), [Order Type], '') as NewField;

LOAD [Product],[Product Type],[Product Number],[Order],[Order Type],[Order Number] as Field

resindent table;

Re: PurgeChar()

nihhalmca — Wed, 30 Nov 2016 09:30:45 GMT

Hi Sunny, its working with small changes.

LOAD *,

Replace(Replace(Field, '[Product Type],', ''), '[Order Type],', '') as NewField;

LOAD [Product],[Product Type],[Product Number],[Order],[Order Type],[Order Number] as Field

resindent table;

Re: PurgeChar()

sunny_talwar — Wed, 30 Nov 2016 09:32:57 GMT

The way you have written this, it should remove it....

Re: PurgeChar()

nihhalmca — Wed, 30 Nov 2016 09:35:15 GMT

I written this to remove double commas however not working.

Replace(Replace(Replace(Field, '[Product Type]', ''), '[Order Type]', ''), ',,', ',')as NewField;

Re: PurgeChar()

sunny_talwar — Wed, 30 Nov 2016 09:37:11 GMT

This seems to be working for me

Table:

LOAD *,

Replace(Replace(Field, '[Product Type],', ''), '[Order Type],', '') as NewField;

LOAD * Inline "

Field

[Product],[Product Type],[Product Number],[Order],[Order Type],[Order Number]

" (delimiter is |);

Re: PurgeChar()

Anonymous — Wed, 30 Nov 2016 09:39:53 GMT

Use in following way

Replace(Replace(Field,' [Product Type],', ''),' [Order Type],' ,'') as NewField;

Re: PurgeChar()

nihhalmca — Wed, 30 Nov 2016 09:44:31 GMT

Yes i understood but my case is not working.

Actually i am doing field value like [Product],[Product Type],[Product Number],[Order],[Order Type],[Order Number] by using of concat.

'[' & concat(distinct [Field], '],[') & ']'

Re: PurgeChar()

sunny_talwar — Wed, 30 Nov 2016 09:46:52 GMT

Why don't you use set analysis here?

'[' & Concat(DISTINCT {<Field -= {'[Product Type]', '[Order Type]'}>} [Field], '],[') & ']'

Re: PurgeChar()

swuehl — Wed, 30 Nov 2016 09:51:43 GMT

Or if you need to handle this in the LOAD script:

'[' & concat(distinct If( not Match([Field],'Product Type','Order Type'),[Field]) , '],[') & ']'

Re: PurgeChar()

its_anandrjs — Wed, 30 Nov 2016 10:21:59 GMT

Dear Nikhal,

Can you share some sample app which should clone of your app but hide the main things that you wont share it.

Regards

Anand

Re: PurgeChar()

nihhalmca — Wed, 30 Nov 2016 10:51:05 GMT

Hi Sunny,

Can we use setanalysis at back end (edit script), i think no.

Actually I have to load these values into variable and use that variable in some other load stmt.

Previous code is working if i see values in listbox however i am loading those values into variable by peek() like vVariable = peek('Field').

Re: PurgeChar()

sunny_talwar — Wed, 30 Nov 2016 10:52:23 GMT

Stefan provided the back end alternative using if statement