topic Counting Occurrences of a String in QlikView

Counting Occurrences of a String

Wed, 25 May 2011 12:53:51 GMT

All,

How do I count an occurrence of a string? I thought it would be straightforward, but I must be missing something?

To count the total number of locations in a warehouse I have used Count([Site_Details LOC_CODE]) which works just fine. But to count the number of empty locations, I have used Count(If(LOC_STAT='Empty',[Site_Details LOC_CODE)). But surprisingly, I get the same count as for the total number of locations?

What am I doing wrong?

Cheers

Ian

Counting Occurrences of a String

erichshiino — Wed, 25 May 2011 13:13:07 GMT

It may depend on your data model. If you select LOC_STAT = 'Empty' on a list box, do you get the right results?

You can also try to make it with set analysis

Count( {< LOC_STAT={Empty} [Site_Details LOC_CODE)

If it doesn`t work, can you share some sample app or data so we can try to reproduce it?

Regards,

Erich

Counting Occurrences of a String

pat_agen — Wed, 25 May 2011 13:15:51 GMT

hi,

syntaxically it looks ok but your data model will impact how counts are done in many ways. Simplest way to test what is happening is make two listboxes, one with LOC_STAT the other with LOC_CODE.

What can you see in these listboxes? If you select the value "Empty" in the LOC_STAT box does the LOC_CODE listbox change.

Going a bit further create a chart with a single dimension being your LOC_CODE. Add two expressions, the first being simply "1" with totals = sum, this will give you a count of all distinct LOC_CODEs, the second being =count(LOC_CODE) again with totals=sum, this will give you a count of total records by LOC_CODE.

Again select values on your LOC_STAT listbox and see what happens here.