topic convert alpha numeric to numeric format in QlikView

convert alpha numeric to numeric format

rupaliqlik — Tue, 21 Sep 2021 08:24:31 GMT

Hi Team,

I want to trim alpha numeric Material_Document_CID and convert as number.

I have given below example where I expect column B as result.

I have written below expression but it is not giving me correct result.

Replace(trim(replace(PurgeChar(material_document_cid,'G,S,A,P')-right(material_document_cid,4),0,' ')),' ',0)

Qliksense result:

0 is not coming in my result.please guide me how to write expression.

Best Regards,

Rupali Ethape

Re: convert alpha numeric to numeric format

MayilVahanan — Tue, 21 Sep 2021 08:53:22 GMT

Try like below

Hope all ur Material_Document_CID has same length, in that case try like below

=Mid(Material_Document_CID, 6, 9)

ex: Mid('GSAP03066585602021', 6, 9)gives 306658560

Re: convert alpha numeric to numeric format

Taoufiq_Zarra — Tue, 21 Sep 2021 09:06:56 GMT

@rupaliqlik maye be :

mid(Material_Document_CID,5,len(Material_Document_CID)-7)

keepchar(left(Material_Document_CID,len(Material_Document_CID)-4),'0123456789')

output:

load *,keepchar(left(Material_Document_CID,len(Material_Document_CID)-4),'0123456789') as New1,mid(Material_Document_CID,5,len(Material_Document_CID)-7) as New2 inline [ Material_Document_CID GSAP03066585602021 GSAP03066225622021 ]

Re: convert alpha numeric to numeric format

rupaliqlik — Tue, 21 Sep 2021 10:28:32 GMT

Hi Taoufiq,

Your result is correct but can you remove initial 0 from your result.

my expecting result I have shown in column B.

Best Regards,

Rupali

Re: convert alpha numeric to numeric format

Taoufiq_Zarra — Tue, 21 Sep 2021 10:31:03 GMT

Hi @rupaliqlik

you can add num(), like

load *,num(keepchar(left(Material_Document_CID,len(Material_Document_CID)-4),'0123456789')) as New1,num(mid(Material_Document_CID,5,len(Material_Document_CID)-7)) as New2 inline [ Material_Document_CID GSAP03066585602021 GSAP03066225622021 ]

output: