topic Re: Reading Digits from string in QlikView

Reading Digits from string

Anonymous — Fri, 24 Jul 2015 04:42:13 GMT

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

Income	Value-1	Value-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

Re: Reading Digits from string

manojkulkarni — Fri, 24 Jul 2015 06:51:45 GMT

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.

Re: Reading Digits from string

crusader_ — Fri, 24 Jul 2015 07:03:30 GMT

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

Re: Reading Digits from string

Kushal_Chawda — Fri, 24 Jul 2015 07:51:43 GMT

Please see the attached

Re: Reading Digits from string

maxgro — Fri, 24 Jul 2015 20:11:39 GMT

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;

Re: Reading Digits from string

antoniotiman — Fri, 24 Jul 2015 22:45:41 GMT

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
];

Re: Reading Digits from string

Anonymous — Mon, 27 Jul 2015 02:55:00 GMT

Thank you Kush, it was what i looking for.

Re: Reading Digits from string

Anonymous — Mon, 27 Jul 2015 02:56:49 GMT

Thank you

Re: Reading Digits from string

jagan — Mon, 27 Jul 2015 03:44:15 GMT

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

];