Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Reading Digits from string

Hi all, in my table i have a column which contains digit and string as below:

IncomeValue-1Value-2
1000RM to 2000RM

Above RM3200

3000
RM1000 to RM 3000

I need to read the digits, and store them in new columns, for example in first row, 1000 must be store in Value-1 and 2000 in Value-2,

in second row, 3200 must be store in Value-1.

It would be appreciated if anyone can help.

Regards,

Amir

8 Replies
manojkulkarni
Partner - Specialist II
Partner - Specialist II

Use KeepChar or PurgeChar function.


KeepChar(s1 , s2)

Returns the string s1 less all characters not contained in string s2.

Example:

keepchar ( 'a1b2c3','123' ) returns '123'



Use KeepChar or PurgeChar function, both will help you in skipping special charecters. Using Keepchar you can keep (e.g. 0-9 and Aa-Zz) required charecters and using purgechar you can actually purge/skip unwanted charecters.

crusader_
Partner - Specialist
Partner - Specialist

Hi,

source:

LOAD

  Income,

  if(wildmatch(Income,'*to*'), keepchar(left(Income, Index(Income,'to')),'0123456789'),

  keepchar(Income,'0123456789')) as [Value-1],

  if(wildmatch(Income,'*to*'), keepchar(right(Income, Index(Income,'to')),'0123456789')

  ) as [Value-2]

;

LOAD Income,

     [Value-1],

     [Value-2]

FROM

[https://community.qlik.com/thread/173820]

(html, codepage is 1251, embedded labels, table is @1);

PFA my solution.

Regards,

Andrei

Kushal_Chawda

Please see the attached

maxgro
MVP
MVP

1.png



s:

LOAD

Income

FROM

[https://community.qlik.com/thread/173820]

(html, codepage is 1252, embedded labels, table is @1);

s2:

LOAD

Income,
SubField(IncomeNum, '|', 1) as [Value-1],
SubField(IncomeNum, '|', 2) as [Value-2];

LOAD

Income, Concat(IncomeNum, '|') as IncomeNum

Where len(trim(IncomeNum)) > 0

Group By Income;

NoConcatenate LOAD

Income, KeepChar(subfield(Income, ' '), '0123456789') as IncomeNum

Resident s;

DROP Table s;

antoniotiman
Master III
Master III

Load Income,

SubField(Temp,' ',1) as [Value-1],
If(Index(Temp,' ') > 0,SubField(Temp,' ',-1)) as [Value-2];
LOAD *,
Ltrim(KeepChar(Income,' 0123456789')) as Temp
Inline [
Income
1000RM to 2000RM
Above RM3200
3000 
RM1000 to RM 3000
]
;

Anonymous
Not applicable
Author

Thank you Kush, it was what i looking for.

Anonymous
Not applicable
Author

Thank you

jagan
Luminary Alumni
Luminary Alumni

Hi,

Try this simple script

LOAD

ActualNumber,

SubField(Temp, '|', 1) AS Value1,

SubField(Temp, '|', 2) AS Value2;

LOAD Number AS ActualNumber,

KeepChar(Replace(Number, 'to', '|'), '0123456789|') AS Temp

Inline [

Number

1000RM to 2000RM

Above RM3200

3000 

RM1000 to RM 3000

];