Count Function: Different results Inload vs. Excel

[#1]

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?

[swuehl · Accepted solution]

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?

[sunny_talwar]

Would you be able to share the Excel file?

[#4]

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

Thanks @swuehl

But how is that the second expression works with excel?

[swuehl]

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

[#6]

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

E.g. for years?

[swuehl]

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])

[#8]

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...

[swuehl]

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])