Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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
Please see the attached
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;
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
];
Thank you Kush, it was what i looking for.
Thank you
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
];