Issue with number of records and filtering in Calculation views

[former_member184969]

Hi Experts

Here is a case I cannot explain nor solve : filters are just not working in HANA the way they are supposed to...

We have a base view(CV_STEP1TO5_NO_AGGR_TEST) which has a calculated column (FILTER_1TO5_OK) with 2 distinct values ('ok' and 'ko') with 'ok' having a count of 25,545,855 and 'ko' having a count of 356,369.

We use this base view as data source in a projection of other view (CV_STEP6TO8_NO_AGGR_TEST).

We have filtered the data on this projection with filter on column FILTER_1TO5_OK = 'ok'.

That means the data preview on the 'upper 'projection in the view CV_STEP6TO8_NO_AGGR_TEST should show the data only for 'ok' with count 25,545,855.

But the problem is, the count shows 25,902,224 which is sum of 'ok' and 'ko' count (25,545,855 + 356,369) which is pretty strange.

The images for the above case are attached.

Base View (CV_STEP1TO5_(...))	Upper View (CV_STEP6TO8...)

We tried several kind of flags (numeric, chars..) , several kind of base view (calc dimension, calc cube, attribute) with the same results.

This is a bit maddening, and probably there's a silly stupid explanation for that, beyond my reach.

Any hint is more than welcome.

Thank you.

[muthuram_shanmugavel2 · Accepted solution]

Hi Marie,

Your "IF" Condition logic is fine

but You have applied the "IF" condition logic in Calculated column - Default Projection Node.

So the records are not getting filtered.

Please Use this "IF" Condition in Filter Expression - Projection node (Drag and Drop from Panel)

In Filter Expression, Write your Logic

After activation, Now check your data. The data will be filtered based on Filter expression (OK)

Thanks & Regards,

Muthuram

[former_member184969 · Accepted solution]

Thanks Muthuram.

In fact, I made a wrong choice in my page setup. The horizontal scroll bar is only available when directly clicking on my post.

If you have a closer look at the right part of the table , you may see that in the upper view, I am using filter condition :

1. in base view (left side), I make a simple calculated column which is saying  'ok' or 'ko'.

2. in upper view (right side) , I am consuming the bas view and filtering (in filter expression) on the 'ok' or 'ko' value.

I'll try to edit my 1st post so that it gets easier to read.

Thanks for usefull answer, anyway.

[muthuram_shanmugavel2 · Accepted solution]

Hi Marie,

I can able to see the 2nd Calculation View - CV_STEP6TO8_NO_AGGR_TEST.

In Calc View, CV_STEP6TO8_NO_AGGR_TEST,

1. Can you please check Data Preview output @ Projection_1 Node level (Row Count for Value OK)

2. If Row count value is correct in Projection_1 Node,

    Then It might be some Problem in Join Node (Projection_1 & Projection_2)

    Please let me know the JOIN condition.

Thanks & Regards,

Muthuram

[former_member184969 · Accepted solution]

The figures I showed is for Projection_1.

Then they remain the same until output of upper view (i.e : at least my leftOuter Join is working fine 🙂

I made another test in between... And there is really something I don't get :

Base view (step1to5) tells me I have 25,902,224 records all in all, from  which 25,545,855 have CA_FLAG_OK =  'ok' and 356,369 are 'ko'. Fine.

Then in upper view,..

- if I set a filter  in Projection_1, on CA_FLAG_OK = 'ok' , I get anyway 29,902,224 records (all marked as 'ok' ! I tested that with an SQL query in the editor ) ! Which is WRONG !

- now if I revert the filter, and filter on CA_FLAG_OK='ko', then I duelly get only my 356,369 'ko' records. Which is RIGHT !

... So it seems the filter is not working correctly only in some cases. Which is a bit maddening.

Any clue on this one ?

Thx again for helping ...

[muthuram_shanmugavel2 · Accepted solution]

Hi Marie,

I have implemented the same. For me I am getting correct output.

I don't know why you are getting Incorrect value.

Thanks & Regards,

Muthuram

[former_member184969 · Accepted solution]

So did I with other vioews and tables...

I registered a support request at SAP for this in between. This really looks like a bug.