Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

amir32na
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
Not applicable

Re: Reading Digits from string

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_
Not applicable

Re: Reading Digits from string

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

kush141087
Not applicable

Re: Reading Digits from string

Please see the attached

maxgro
Not applicable

Re: Reading Digits from string

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
Not applicable

Re: Reading Digits from string

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

amir32na
Not applicable

Re: Reading Digits from string

Thank you Kush, it was what i looking for.

amir32na
Not applicable

Re: Reading Digits from string

Thank you

jagan
Not applicable

Re: Reading Digits from string

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

];