Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Filter strings in a formula


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

1 Solution

Accepted Solutions
Not applicable
Author

Hi Jagan,

first of all thanks for your help.

What do I expect:

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

july.jpg

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

X

Y    Y

GATE*

ACTR*

the sum of 

X

Y    Y

and

GATE*

ACTR*

should be

sum (Nettoumsatz)

but it is not, see table.

View solution in original post

14 Replies
tresesco
MVP
MVP

Try like:

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

Not applicable
Author

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

jagan
Luminary Alumni
Luminary Alumni

Hi,

Try below set analysis expressions

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

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


Hope this helps you.


Regards,

jagan.

tresesco
MVP
MVP

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

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

jagan
Luminary Alumni
Luminary Alumni

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.

Not applicable
Author

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

PrashantSangle

Hi,

Try like this

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

Regards,

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
Not applicable
Author

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.

jagan
Luminary Alumni
Luminary Alumni

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.