topic Re: Count Function: Different results Inload vs. Excel in QlikView

Count Function: Different results Inload vs. Excel

Wed, 25 Nov 2020 16:16:04 GMT

Hey Guys,

when I do:

LOAD * INLINE [

company, 1.1.

A, 2

B, -2

C, 0

D, 2

E, -2

F, -2

G, 0

];

Both expressions work:

=count({<[1.1.] = {'-2'}>}[1.1.])

=count(if(match([1.1.],'-2'),[1.1.]))

However, when I load this data from an excel sheet only the second works.

Unfortunately I can´t upload the file here...

Any ideas?

Re: Count Function: Different results Inload vs. Excel

swuehl — Wed, 01 Jun 2016 20:14:01 GMT

Data model is identical, a single table with the two fields in both cases?

Field values are exactely the same? No different number formats for -2?

Re: Count Function: Different results Inload vs. Excel

sunny_talwar — Wed, 01 Jun 2016 20:26:09 GMT

Would you be able to share the Excel file?

Re: Count Function: Different results Inload vs. Excel

Wed, 01 Jun 2016 20:27:58 GMT

Well, in Excel the number format was -2,00...

Thanks @swuehl

But how is that the second expression works with excel?

Re: Count Function: Different results Inload vs. Excel

swuehl — Wed, 01 Jun 2016 20:34:50 GMT

The set modifier is much more picky with regard to field value formats.

Re: Count Function: Different results Inload vs. Excel

Thu, 02 Jun 2016 07:09:21 GMT

Can I somehow do set analysis in =count(if(match([1.1.],'-2'),[1.1.])) ?

E.g. for years?

Re: Count Function: Different results Inload vs. Excel

swuehl — Thu, 02 Jun 2016 09:04:43 GMT

You should be able to use set analysis, you just may need to take care of formatting issues:

Count( {<Year = {2016}>} Year)

or with your field maybe

Count( {<[1.1] = {'-2,00'}>} [1.1])

Re: Count Function: Different results Inload vs. Excel

Thu, 02 Jun 2016 11:17:37 GMT

I know that, but what I want is to use set analysis for the other expression with the match function as this is less picky.

Even since I have used the same number format in excel some columns are imported with the wrong format in qlik...

Re: Count Function: Different results Inload vs. Excel

swuehl — Thu, 02 Jun 2016 13:07:49 GMT

You can also try something like

Count( {<[1.1] = {"=[1.1] = -2"}>} [1.1])

or if you want to ignore user selections

Count( {<[1.1] = {"=Only({1} [1.1]) = -2"}>} [1.1])