Match expression in Load statement is not calculating correct counts in KPI chart.

[pgloc2020]

Hello,

I have used 2 filtering clause in the LOAD statement like below-

(qvd)

where [Application_Type]= 'Standard'
and match ([City], 'PA', 'CA, 'MD', 'TX', 'VA') ;

It ran without any error and in the filter Pan,  I can see only those 5 cities and Appl type = Standard as expected. But in the KPI, I am not getting correct counts. However, if I manually select those 5 cities or application type, it calculates current counts. 

Below are the screenshot where you can see the difference in the counts-

I manually exported the data from the database in the spreadsheet and verify the counts. The total counts should be 45,288. Not sure from where those extra 4190 counts are adding up. What I am missing?

[Or]

Are there other tables in this app? If so, one of them may be adding applications or creating an unexpected join which changes the total.

[pgloc2020]

Yes, there are other columns too. But I really do not want to create multiple QVDs when user want one dashboard having just those 5 cities and the one specific type application,  and another dashboard displaying all the Cities and all the Application Types. 

Which is why, I am trying to use where clause in the QVD. 

Is there any way I can achieve the correct counts?

[steeefan]

Seems impossible to say without any further knowledge about code, loaded data, expresssion etc. Can you provide any of that?

[Or · Accepted solution]

You could just adjust your expression for this KPI with set analysis, that is how one typically handles the need to count specific subsets of the overall data.

Count({< [Application_Type] = {'Standard'}, [City] = {'PA', 'CA, 'MD', 'TX', 'VA'} >} ApplicationID)

Otherwise, it's impossible for us to say how you might be able to adjust this because we have no way of knowing what your data model is like and where the "extra" figures are coming from.

[pgloc2020]

Using the expression it works.

Below is my load statement though-

[APPLICATION_TYPEMapping]:

MAPPING LOAD * INLINE

[

APPLICATION _TYPEMapping-FROM, APPLICATION _TYPEMapping-TO

];

[Applicant]:

LOAD

                [APPL_NUMBER],

                [CITY],

                [APPLICANT_NAMES],

               [CORP_FLG],

               [REG_DECISION_DT],

             If(IsNull([APPLICATION _TYPE]), 'Blank', APPLYMAP( ' APPLICATION _TYPEMapping',   [APPLICATION_TYPE])) AS [APPLICATION _TYPE]

FROM [lib://:DataFiles/Applicant.qvd]

(qvd)

where [APPLICATION _TYPE]= 'Standard'

and  match ([CITY], 'PA', 'CA', 'MD', 'TX', 'VA') ;

[steeefan]

In your script, when performing the LOAD, you're filtering on the original value of APPLICATION_TYPE. In the expression in the chart, that's then after running the LOAD and performing ApplyMap(). The filter is then on the mapped value.

What are the values you are mapping from and to?

[pgloc2020]

There are some "Null" values in the Application Type, which is why I am adding text "Blank' to those Null values by applying mapping to from.