Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
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

];