topic Re: Get only the first possible Number in QlikView

Get only the first possible Number

psk180590 — Wed, 25 Nov 2020 16:16:04 GMT

Hello All,

I have data in Text format which is not consistent. Few examples below:

5/6
10 packets
More than the standard, 12 packets available
5*3 packets
5packets

The data looks very comical, but i have to some way take out only the first possible digit.

From the above example data

5/6 - 5
10 packets - 10
More than the standard, 12 packets available - 12
5*3 packets - 5
5packets - 5

The numbers in bold is my expected result.

I'm not sure if this can really be achieved. Please throw in some suggestions or if any previously faced encounters.

Thanks in Advance!!

Sai.

Re: Get only the first possible Number

Anil_Babu_Samineni — Wed, 08 Nov 2017 15:23:22 GMT

From 5/6 and 5*3 ?? Why only 5 and why not 6 and 3??

Re: Get only the first possible Number

psk180590 — Wed, 08 Nov 2017 15:27:03 GMT

Good question!

It says

5/6 means 5 packets out of 6 are in order

5*3 means 3 packets should have been present but, instead we have 5.

Re: Get only the first possible Number

sunny_talwar — Wed, 08 Nov 2017 15:28:54 GMT

Try this

Table:

LOAD *,

FindOneOf(Value, '0123456789') as Start,

FindOneOf(Mid(Value, FindOneOf(Value, '0123456789')), 'abcdefghijklmnopqrstuvwxyz!@#$%^&*()_+}{":?><~`-= /\][.,;')-1 as End,

Mid(Value, FindOneOf(Value, '0123456789'), FindOneOf(Mid(Value, FindOneOf(Value, '0123456789')), 'abcdefghijklmnopqrstuvwxyz!@#$%^&*()_+}{":?><~`-= /\][.,;')-1) as Req;

LOAD * INLINE [

Value

5/6

10 packets

"More than the standard, 12 packets available"

5*3 packets

5packets

];

Re: Get only the first possible Number

sunny_talwar — Wed, 08 Nov 2017 15:33:35 GMT

For the sake of clarity, you can see this also

Table:

LOAD *,

Mid(Value, Start, End) as Req;

LOAD *,

FindOneOf(Value, '0123456789') as Start,

FindOneOf(Mid(Value, FindOneOf(Value, '0123456789')), 'abcdefghijklmnopqrstuvwxyz!@#$%^&*()_+}{":?><~`-= /\][.,;')-1 as End;

LOAD * INLINE [

Value

5/6

10 packets

"More than the standard, 12 packets available"

5*3 packets

5packets

];

Re: Get only the first possible Number

trdandamudi — Wed, 08 Nov 2017 15:46:06 GMT

May be as below on the chart (Assuming there will be only * and / in the data):

Re: Get only the first possible Number

psk180590 — Wed, 08 Nov 2017 15:47:44 GMT

stalwar1‌

Thanks for the suggestion, it works perfectly except for some cases

Total 3 - Gives result as Null where as the answer should be 3

2Packets 100gm - Gives result as 2P where as the answer should be only 2

Also when there is empty space after the Text example 4packets (followed by space) - Gives result as 4p where as the answer should be 4.

Regards

Sai.

Re: Get only the first possible Number

sunny_talwar — Wed, 08 Nov 2017 15:55:25 GMT

Not sure what is wrong with 4 packets, but rest of them work now

Table:

LOAD *,

Mid(Value, Start, End) as Req;

LOAD *,

FindOneOf(Value, '0123456789') as Start,

FindOneOf(Mid(Value, FindOneOf(Value, '0123456789')+1) & '/', 'abcdefghijklmnopqrstuvwxyz!@#$%^&*()_+}{":?><~`-= /\][.,;ABCDEFGHIJKLMNOPQRSTUVWXYZ') as End;

LOAD * INLINE [

Value

5/6

10 packets

"More than the standard, 12 packets available"

5*3 packets

5packets

Total 3

2Packets 100gm

4packets

];

Re: Get only the first possible Number

antoniotiman — Wed, 08 Nov 2017 16:35:32 GMT

Try this

LOAD Value,Left(Field,FindOneOf(Field,'*/@')-1) as Field;
LOAD Value,Replace(LTrim(KeepChar(Value,'0123456789*/ ')),' ','@')&'@' as Field INLINE [
Value
5/6
10 packets
"More than the standard, 12 packets available"
5*3 packets
5packets
Total 3
2Packets 100gm
4p];

Re: Get only the first possible Number

MarcoWedel — Thu, 09 Nov 2017 23:45:39 GMT

Hi,

another solution might be:

mapNonNumToSpace:
Mapping LOAD Chr(RecNo()), ' ' AutoGenerate 65535 Where not IsNum(Chr(RecNo()));

table1:
LOAD Text(Text) as Text,
    SubField(Trim(MapSubString('mapNonNumToSpace',Text)),' ',1) as Number
INLINE [
    Text
    No number here
    1
    23
    345
    1a2b3c
    5/6
    10 packets
    "More than the standard, 12 packets available"
    5*3 packets
    5packets
    Total 3
    2Packets 100gm
    "4packets    "
    "    6packets"
];

hope this helps

regards

Marco