Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
Not applicable

Set analysis help needed - Word that starts with or include

Hi guys

I have an item master. Each item has an ITEMCODE. I want to do an EXPRESSION in a STRAIGHT TABLE as the following:

=sum(COST) to get a VALUATION of my stock. The problem is that I only want the valuation of items that has a certain ITEMCODE.

I only want the value of items that starts with a "F" or "FZA". I only know how to do a set analysis on a whole ITEMCODE (see below).

Obviously would this not help as I will have to list every ITEMCODE I want. Is there a way that I can do a set analysis on an ITEMCODE that starts with "F" or "FZA"?

sum({<ItemCode={F10024000'}>} Cost)



Tags (1)
1 Solution

Accepted Solutions
Not applicable

Set analysis help needed - Word that starts with or include

I have only used set analyis when looking for an exact value e.g. Sum({<dsc={'FSA'}>} VALUATION)

In this instance can you try using an if statement





sum(if(left(ITEMCODE,3)='FZA' or left(ITEMCODE,1)='F',VALUATION))





View solution in original post

5 Replies
Not applicable

Set analysis help needed - Word that starts with or include

I have only used set analyis when looking for an exact value e.g. Sum({<dsc={'FSA'}>} VALUATION)

In this instance can you try using an if statement





sum(if(left(ITEMCODE,3)='FZA' or left(ITEMCODE,1)='F',VALUATION))





View solution in original post

Not applicable

Set analysis help needed - Word that starts with or include

Thank you

This worked perfect

Set analysis help needed - Word that starts with or include

Hello Christo,

For what it's worth, set analysis would work like

sum({<ItemCode={"F*","FZA*"}>} Cost)


Take into account that "F*" will return FZA* results as well.

Regards.

gandalfgray
Valued Contributor II

SV:Re: Set analysis help needed - Word that starts with or include

If you really wants COST for items like you specified: ITEMCODE that starts with "F" or "FZA"

you only need ITEMCODE that starts with "F" (everyone agree that "FZA" starts with "F" OK?)

so

sum(if(left(ITEMCODE,1)='F',COST))

would do, it will give the same result as

sum(if(left(ITEMCODE,3)='FZA' or left(ITEMCODE,1)='F',COST))

SusanneScherer
New Contributor II

Re: Set analysis help needed - Word that starts with or include

Thank you. This worked for me as well.