convert alpha numeric to numeric format

[rupaliqlik]

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

[MayilVahanan]

Hi 

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

[Taoufiq_Zarra]

@rupaliqlik  maye be :

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

or

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
]

[rupaliqlik]

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

[Taoufiq_Zarra]

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: