Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QLIKWORLD LIVE! MAY 16 - 19TH, EARLY BIRD DISCOUNTS! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
rupaliqlik
Creator
Creator

convert alpha numeric to numeric format

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)

rupaliqlik_1-1632212064698.png

Qliksense result:

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

rupaliqlik_2-1632212552530.png

 

Best Regards,

Rupali Ethape

 

4 Replies
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

Thanks & Regards,
Mayil Vahanan R
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
]

 

Taoufiq_Zarra_0-1632215197541.png

 

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
rupaliqlik
Creator
Creator
Author

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:

Taoufiq_Zarra_0-1632220256277.png

 

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉