How to add filter for blank in analytical view

[rajarshi_muhuri]

I wish to apply a filter for "blank" on the analytical view

i.e split the analytical view into 2 analytical view (two paths in calculation view )

one with filter on cost center = blank and another with cost center ( RCNTR ) not eq to blank . I cant seem to do this .

But  it works in SQL as

select rcntr from tablex where rcntr = ''
and
select rcntr from tablex where rcntr <> ''

and wierdly having RCNT IS NULL  as filter seems to have no effect , it even picks up RCNTR where it is blank and also has a value

[Former Member · Accepted solution]

Hi Rajarshi,

This is because NULL and Blank (space) are treated differently. When you do a data preview, if the value appears as '?' (or whatever you have set in HANA Studio properties) then that is considered to be a NULL value. However if you have a genuine blank/space there, it wouldn't show up as ? (Null).

To get around this problem, two options:

a. Set your filter as 'Equal to' either no space, or 1 space hit or 2 space hit - depending on how many blank spaces you had entered into your costcenter field.

If the insert was as '' - then in your filter screen dont hit the space bar

If the insert was as ' ' - then in your filter screen hit the space bar once

..and so on...

OR

b. simply convert all your blank spaces to NULL with the SQL statement:

UPDATE tablex SET rcntr=NULL where rcntr='';

and then create filters with 'IS NULL' and 'IS NOT NULL'.

Thanks,

Anooj

[rajarshi_muhuri · Accepted solution]

Anooj , your suggestions were useful in general .. but did not yet work in my case ..

I had mentioned null , even though I knew its treated differently as because ISNULL was not working. Since none of the RCNTR was null , so effectively a filter on RCNT = ISNULL should have had no rows pulled up . But having this filter on the analytical view layter does not work but works on the projection layer with the same filter.

but anyhow RNCTR is nvarchar(10) , so i tried all combinations

1. no space

2. 1 space

3. 9 space

4. 10 spaces

but none worked .

Also , these tables data is via SLT. So I dont think I can alter the table structures as the ECC table and HANA table would be out of sync (not sure , but as afar as I remember from SLT documentation.. that this breaks SLT) .

However it might work , if I myself create the table in HANA, alter it , and then the point SLT to it . In fact this could be key to solving many other issues .

[Former Member · Accepted solution]

Hi,

With option 1, I tried on my analytical view with ISNULL on a column that had no null values and a data preview did not return any values. It also worked in instances where i had blank spaces and used "equal to". Not sure why it isnt working in your case - wonder if it is the revision (I am on 35).

On my second option, I wasn't referring to altering the table structure. I was only asking you to alter the data within the table. Now that I know you are using SLT to populate this table, it isnt a good idea to directly alter the data within HANA.

Since you are using SLT, why not create a transformation rule in the SLT system for the costcenter column alone?

Say if you are using the event based rule, may be you could convert values with just blank spaces in the cost center field to 0 using something similar to the following in your ABAP include - You have details on this in the TZH300 document:

if <wa_s_<table_name>>-rnctr is null.

  <wa_r_<table_name>>-rnctr = 0.

endif.

Remember, as far as i know abap considers just blank/space characters in a character field and just 0 value in a numeric field as NULL - different to how it works in HANA.

Further, in your analytical view use "equal" and "notequal" to 0 as filters.

Thanks,

Anooj

[rajarshi_muhuri · Accepted solution]

This client is on revision 26. SO I am hoping its the revision . This is driving me crazy

The EXACT filter DOES NOT work in the data foundation layer of the analytical view , but works when i use it on a projection of the analytical view .

The Filter on the analytical view :

But it works when I use the same filter on the projection layer  ( in a calc view) of that same analytical view

Unfortunately , upgrading to higher revision is not an option .

[Former Member · Accepted solution]

Hey looks like a bug to me - in your second screenshot the statistics for RACCT field shows the max. value selected is well beyond what you had set in the filter condition. Worth checking if it is only a bug with the data preview functionality - you could check in the SQL editor I suppose.

Otherwise, strongly recommend moving up the revision ladder.

Thanks,

Anooj

[Former Member · Accepted solution]

Hi Rajarshi,

Rev 26 has a lot of bugs and after upgrading many such issues have gone. But I still see such issues in the data preview/analysis space in the Analytic view. But if you SQL the SYS_BIC_ column view with the same filter, you may see correct values.

A bitter experience.

/bikas

[Former Member]

Hi Rajarshi,

Yeah, you are right. Its not quite straight FWD. I followed step 1 of what Anooj mentioned and sometimes I do " where rcntr < '0' " or something like that. By SQL standards, it is not the best way, but it is one of the ways.

/bikas

[justin_molenaur2]

I know this is a very old thread, but I am seeing similar issues in Rev 62.

In a graphical calculation view, I am trying to use a projection on an analytic view using MATNR != '', which I mean to say EXCLUDE any rows where material is blank.

The same SQL Statement like below, returns the expected result.

SELECT COUNT(*)

FROM TABLE

WHERE MATNR <> ''

However, the same result cannot be observed from the calculation view, I get different results, some with blank materials.

SLT handles the data loads, and this particular column is set to 'not null', so there are no nulls present in the data, so that can't be the problem.

Regards,

Justin

[justin_molenaur2]

Simply put, these two statements are not equivalent.

MATNR != ''

AND

MATNR != ' '

Once I identified that the column that the filter was being applied to was actually 'blank' and not as whitespace, changed the syntax accordingly and it worked fine.

Regards,

Justin

[Former Member]

Justin : your thing is correct . its just that at that time HANA had a bug that prevented from applying the filter correctly .

Also current SP 06 Studio has a bug , if I try to change the measure in  a union to ZERO. It forces the measure to be a attribute , any one elese having this issue