topic Find Number within Text in QlikView

Find Number within Text

renjithpl — Fri, 26 Mar 2010 08:28:56 GMT

hi......

I have to FIND only number within the text, from both fields below, and compare it and if it doesn't match then gives "error".

This is the formula I used in excel: =ISNUMBER(FIND(Sheet1!D2,Sheet1!C2))

I tried =If(Num(ResponCatCode)<>Num(ResponsiveCategories), 'Error')

But its not working properly

Is there any script I can use…

for example if responsiveCategories has "4-.... " and if ResponCatCode says "2-..." it should say error

ResponsiveCategories	ResponCatCode
4-Communication regarding Enron financial issues
2-Communication between Enron and its auditors - reference to such communications	2-Communication between Enron and its auditors - reference to such communications 4-Communication regarding Enron financial issues

Find Number within Text

Fri, 26 Mar 2010 09:18:29 GMT

Hi,

see the attached example for an idea. In the QlikView help file you will find more information about "String Functions".

Good luck!

Rainer

Find Number within Text

justinasp — Fri, 26 Mar 2010 09:26:53 GMT

Hi,

one workaround that I think of could be:

=IF(KEEPCHAR(ResponCatCode,'0123456789')<>KEEPCHAR(ResponsiveCategories,'0123456789'),'FALSE')

Justinas

Find Number within Text

renjithpl — Fri, 26 Mar 2010 11:36:10 GMT

Thanks a lot Justinas, it worked........ 🙂

Find Number within Text

renjithpl — Fri, 26 Mar 2010 11:37:21 GMT

it really helped me to understand some thing more about strings... thanks a lot

Find Number within Text

justinasp — Fri, 26 Mar 2010 13:44:41 GMT

That what this forum is for!

Good luck,

Justinas

Find Number within Text

renjithpl — Mon, 29 Mar 2010 12:55:32 GMT

Hi Justinas,

Is there any option for me to find the character individually,

as per your example.

KEEPCHAR(abcd1agc2egee3, '0123456789') returns 123

I would be greatful if you could tell me some method where it returns 1, 2, and 3 separately. it should not read as one hundred twenty three.

Thanks.

Find Number within Text

justinasp — Mon, 29 Mar 2010 15:01:05 GMT

Hi,

you shoul try to experiment with some "String functions". For example:

=MID(vTest,FINDONEOF(vTest,'0123456789', [write number here]),1)
=MID(KEEPCHAR(vTest,'0123456789'), [write number here], 1)

and so on..

Cheers,

Justinas

Find Number within Text

renjithpl — Mon, 05 Apr 2010 08:33:28 GMT

hi...

can you tell me whats vTest, and what it does, its showing bad field name.

Thanks...

Find Number within Text

justinasp — Tue, 06 Apr 2010 08:57:13 GMT

Hi,

vTest is a name for a field or variable. In your case it should be ResponCatCode or smth.

Take care,

Justinas

Find Number within Text

renjithpl — Wed, 07 Apr 2010 12:52:02 GMT

Thanks i got what is vTest. And what number i should put in [write number here]. What do you mean by [write number here] ?

Find Number within Text

justinasp — Thu, 08 Apr 2010 07:36:25 GMT

In this place you should enter which number in a row you want to get: 1 - you'lll get the first digit in string vTest, 2 -second, ect.

I attach an example you can try to play with. Hope it will explain your issue.

Justinas

Find Number within Text

renjithpl — Fri, 09 Apr 2010 07:44:32 GMT

Hi Justinas,

your example helped me a lot in learning about strings. But i am quiet confused with my requirements. So Can you please attach me an example.

ABC and XYZ are both columns

ABC XYZ

4; 5 4; 5; 6; 7

Here first two strings are matching in both columns (4; 5 and 4; 5). But XYZ has more apart from 4; 5 (6; 7). If any thing more than ABC then it should give an Error.

Therefore it can be (2; 3 and more than 2; 3), (2; and more than 2; ) there are lot of probabilities. You can give me the example for the above table..

Thanks a lot Justinas.

You have been so good to me so far. it would be really greatful.

Find Number within Text

justinasp — Mon, 12 Apr 2010 07:32:27 GMT

I don't understant your issue this time.

However comparing two sequencies of digits sometimes it's enough to take sums of those rows (this case use the most primitive function SUM()).