topic Re: Filter strings in a formula in QlikView

Filter strings in a formula

Tue, 02 Sep 2014 08:32:35 GMT

Good morning,

I am new to QV and need some help with filterring strings in a formula:

Given is the data in the following table: TB01_20140902_101356.xls

which shows the turnover and qty. per material and product hierache.

I would like to sum the turnover with the following formulas:

1. if (Produkthierarchie = 'X?*' , 'Y?*',sum(Nettoumsatz))

2. if (Produkthierarchie like 'GATE*' , 'ACTR*',sum(Nettoumsatz))

but both deliver the same result? Could you please help me resolving this?

Thanks, Matthias

Re: Filter strings in a formula

tresB — Tue, 02 Sep 2014 08:36:53 GMT

Try like:

=Sum( if (WildMatch(Produkthierarchie = 'X?*' , 'Y?*'), Nettoumsatz))

Re: Filter strings in a formula

Tue, 02 Sep 2014 08:43:34 GMT

Thanks for your answer, but this returns 0 as the result. my formula returned the total turnover.

Re: Filter strings in a formula

jagan — Tue, 02 Sep 2014 08:44:47 GMT

Hi,

Try below set analysis expressions

1. sum({<Produkthierarchie ={ 'X?*' , 'Y?*'}>}Nettoumsatz)

2. sum({<Produkthierarchie ={ 'GATE*' , 'ACTR*'}>}Nettoumsatz)

Hope this helps you.

Regards,

jagan.

Re: Filter strings in a formula

tresB — Tue, 02 Sep 2014 08:46:04 GMT

Correction: replace '=' with a comma and try like:

=Sum( if (WildMatch(Produkthierarchie , 'X?*' , 'Y?*'), Nettoumsatz))

Re: Filter strings in a formula

jagan — Tue, 02 Sep 2014 08:46:07 GMT

Hi,

What is ? in your search string? It is there in your value? If it is in value then expressions in my previous reply works

or try below

1. sum({<Produkthierarchie ={ 'X*' , 'Y*'}>}Nettoumsatz)

2. sum({<Produkthierarchie ={ 'GATE*' , 'ACTR*'}>}Nettoumsatz)

Regards,

Jagan.

Re: Filter strings in a formula

Tue, 02 Sep 2014 08:51:19 GMT

Try this .. Sum( if (WildMatch(Produkthierarchie,'X?*','Y?*'), Nettoumsatz))

Re: Filter strings in a formula

PrashantSangle — Tue, 02 Sep 2014 09:01:33 GMT

Hi,

Try like this

Sum(if(wildmatch(Produkthierarchie,'X?*' , 'Y?*'),Nettoumsatz))

Regards,

Re: Filter strings in a formula

Tue, 02 Sep 2014 09:02:11 GMT

Hi Jagan,

the set analysis expressions at leased deliverred a result, but I think it is not correct.

I have checked that all records in my table contain a prod. Hierach. so the sum of both sesults sould deliver the total turnover, but they do not.

The '?' in the formula is because I do not know at which plave the second 'Y' is thin the prod. hierach., the content of that field can be:

1. X

2. Y Y

3. GATE XXXX XXXX

4. ACTR XXXX XXXX, like you see in the attached Excel.

If I take the '?' out of your formular it returns '0'

Thanks.

Re: Filter strings in a formula

jagan — Tue, 02 Sep 2014 09:24:05 GMT

Hi,

You have four spaces in the Excel, so try this expression

1. sum({<Produkthierarchie ={ 'X*' , 'Y????Y'}>}Nettoumsatz)

Hope this helps you.

Regards,

Jagan.

Re: Filter strings in a formula

Tue, 02 Sep 2014 09:36:48 GMT

Thanks, unfortunately this is not the solution,

The best till now was:

1. sum({<Produkthierarchie ={ 'X?*' , 'Y?*'}>}Nettoumsatz)

2. sum({<Produkthierarchie ={ 'GATE*' , 'ACTR*'}>}Nettoumsatz)

I need to find out why there is the difference in the total summary of both results.

Re: Filter strings in a formula

jagan — Tue, 02 Sep 2014 09:53:59 GMT

Hi,

I didn't get what you are expecting, can you come up with an example and expected value.

Regards,

Jagan.

Re: Filter strings in a formula

Tue, 02 Sep 2014 10:09:26 GMT

Hi Jagan,

first of all thanks for your help.

What do I expect:

the total turnover for the month of July for example was:

The field "Teile" is calculatet by

sum ({<Produkthierarchie ={ 'X?*' , 'Y?*'}>}Nettoumsatz)

"Anlagen" by

sum ({<Produkthierarchie ={ 'GATE*' , 'ACTR*'}>}Nettoumsatz)

"Nettoumsatz" by

sum (Nettoumsatz)

Since all Materials have a prod. hierach. which is either

Y Y

GATE*

ACTR*

the sum of

Y Y

and

GATE*

ACTR*

should be

sum (Nettoumsatz)

but it is not, see table.

Re: Filter strings in a formula

jagan — Tue, 02 Sep 2014 10:18:21 GMT

Hi,

I checked your excel with below expressions,

=sum ({<PH ={ 'X*' , 'Y*'}>}Nettoumsatz) + sum ({<PH ={ 'GATE*' , 'ACTR*'}>}Nettoumsatz)

and

=sum (Nettoumsatz)

both expression values are matching.

Regards,

Jagan.

Re: Filter strings in a formula

Tue, 02 Sep 2014 13:25:35 GMT

Hi Jagan,

had the wrong field, thanks for your help.

Matthias