topic Re: Select only some number with keepchar in QlikView

Select only some number with keepchar

louisernould — Mon, 15 Sep 2014 17:03:10 GMT

Hi everybody,

I have a very big column with letter & numbers and I want to create a second column with only some numbers of the first one.

for exemple This is the table I want

Designation	Code48
AAA 48 BA 128	48
AA14	-
BBB48A214	48

So I tried with the Keepchar function:

Load

Designation

KeepChar(Designation, 48) as code48

From Table

But the problem is, when I try that code, I have this table

Designation	Code48
AAA 48 BA 128	488
AA14	4
BBB48A214	484

Does someone have an idea ? It's the end for me ^^

Thanks,

Louis

Re: Select only some number with keepchar

tresB — Mon, 15 Sep 2014 17:06:07 GMT

Try like:

Load

If(Index(Designation, '48'), 48) as Code48

Re: Select only some number with keepchar

louisernould — Mon, 15 Sep 2014 17:23:41 GMT

YOU ARE MY GOD !

Thank you very much it work !! And moreover it was fast ^^

Tresesco winner !

Re: Select only some number with keepchar

martynlloyd — Mon, 15 Sep 2014 17:56:58 GMT

If you want a robust method, to extract the first group of numbers (variable len), without using a loop, try something like

input:
Load * Inline [
desig
AAA 48 BA 128
AA14
CC24
BBB48A214
];

temp1:
LOAD
PurgeChar(desig,' ') AS desig,
FindOneOf(PurgeChar(desig,' ') , '0123456789') as start,
Mid(PurgeChar(desig,' '),FindOneOf(PurgeChar(desig,' ') , '0123456789'),len(PurgeChar(desig,' '))) as string2,
FindOneOf(Mid(PurgeChar(desig,' '),FindOneOf(PurgeChar(desig,' ') , '0123456789'),len(PurgeChar(desig,' '))) , 'ABCDEFGHIJKLMNOPQRSTUVWXYZ')
-1 as length

Resident input
;

Result:
LOAD
desig,
f(length<=0, mid(desig,start,len(desig) -(start-1)), mid(desig, start, length)) as code
Resident temp1
;

drop Table input, temp1;

regards,

Re: Select only some number with keepchar

MarcoWedel — Mon, 15 Sep 2014 22:56:29 GMT

another one:

Pick(WildMatch(Designation,'*48*'),48) as Code48

Re: Select only some number with keepchar

anbu1984 — Tue, 16 Sep 2014 05:29:35 GMT

If(Designation Like '%48%'), 48) as Code48

Re: Select only some number with keepchar

louisernould — Tue, 16 Sep 2014 08:02:38 GMT

actually, all your answers are correct that's perfect !

Martyn Lloyd, yours is quite hard, but much robust. I needed only a simple solution. But thks anyway, I like the logic of it.

Re: Select only some number with keepchar

Mon, 15 Jun 2015 14:21:32 GMT

Very helpful