topic Re: How to filter for numbers in string value? in QlikView

How to filter for numbers in string value?

Anonymous — Tue, 11 Jul 2017 14:57:01 GMT

Hello fellow Qlik enthusiasts,

Is it possible to filter for numbers in Qlik, even when they are included in string values?

I have a simple table box that shows questions and answers of a questionnaire. For some questions, the participants of that questionnaire were able to respond with custom answers (no predesigned answers to select). In some of those answers are numbers e.g.:

"We have anual costs of more than 2000€"

"The anual costs don't exceed 500€"

Now I want to show all the answers that only include numbers somewhere in the text. The problem is, that the numbers don't have a fixed position since they are included in custom answers.

Is there a way to filter for these answers anyway?

Thank you very much and best regards,

Franziska

Re: How to filter for numbers in string value?

antoniotiman — Tue, 11 Jul 2017 15:46:50 GMT

Hi Franziska,

try like this

If(Len(KeepChar(YourField,'0123456789')) > 0,YourField,' ')

=If(Len(KeepChar('We have anual costs of more than 6543€','0123456789')) > 0,1,0) -> 1

=If(Len(KeepChar('We have anual costs of more than abcd€','0123456789')) > 0,1,0) -> 0

Regards,

Antonio

Re: How to filter for numbers in string value?

MarcoWedel — Tue, 11 Jul 2017 21:40:46 GMT

Hi,

another way to to generate a flag field:

table1:
LOAD *,
    Sign(FindOneOf(Answer,1234567890)) as ContainsNumbers
INLINE [
    Answer
    "We have anual costs of more than 2000€"
    "The anual costs don't exceed 500€"
    "Just some answer without any numbers"
    "Some plain text"
];

Are you also looking for a solution to extract the actual numbers from the string into a separate field?

hope this helps

regards

Marco

Re: How to filter for numbers in string value?

MarcoWedel — Wed, 12 Jul 2017 21:54:44 GMT

Hi,

glad you liked our solutions.

Please close your thread if your question is answered:

Qlik Community Tip: Marking Replies as Correct or Helpful

thanks

regards

Marco