Skip to Content

### Filter out all Nos with corresponding side entries from Fields - Appreciate all the help

I have 2 fields as below: I need to filter out only Nos with Y Signal and the corresponding credit side of it. What I want to see left is just Nos (22,35,36 see below). There is no Countif function to help me and not sure how to go about this. Appreciate all help.

Nos Signal

10 Y - Db
10 # - Cr
22 # - Db
22 # - Cr
24 Y - Db
24 # - Cr
35 # - Db
35 # - Cr
36 # - Db
36 # - Cr
37 Y - Db
37 # - Cr

This is what I want left

Nos Signal

22 # - Db
22 # - Cr
35 # - Db
35 # - Cr
36 # - Db
36 # - Cr

##### Add comment
10|10000 characters needed characters exceeded

### 9 Answers

• Nov 09, 2016 at 09:34 AM

Given the condition that Y will appear as the 3rd character.

Try this:

[Filter]=if(Substr([Object];3;3)="Y") then "Hide" else "Show"

If Y can appear any where in the string and then you need to filter out the data. Try this:

[Filter]=if(Substr([Object];1;Length([Object]))="Y") then "Hide" else "Show"

##### Add comment
10|10000 characters needed characters exceeded
• Nov 09, 2016 at 04:45 PM

Kuldeep, thanks. But the formula is giving me something different. Its recognising Y for all. I just need it to add Y to the corresponding Nos with Cr. Side. If there is no Y in debit side then l want to filter those out.

See what l have and result am looking to get below.

Nos. Signal

10. Y - Db
10. # - Cr
22. # - Db
22. # - Cr
24. Y - Db
24. # - Cr
35. # - Db
35. # - Cr
36. # - Db
36. # - Cr
37. Y - Db
37. # - Cr

This is what I want left

Nos. Signal

22. # - Db
22. # - Cr
35. # - Db
35. # - Cr
36. # - Db

##### Add comment
10|10000 characters needed characters exceeded
• I got this output. Isn't this what you were expecting?

sample.png (51.9 kB)
• Nov 09, 2016 at 06:13 PM

Hi,

Can you try this by tweaking formula provided by Kuldeep:

=If ( ([Nos] = Previous([Nos]) and (Substr([Signal];1;Length(Signal))="#")) then

[Signal]

Thanks,

Jothi

##### Add comment
10|10000 characters needed characters exceeded
• Nov 10, 2016 at 06:17 AM

Jothi its off. I used your formula as below too:

= (([Nos] = Previous([Nos]) and(Substr([Signal];1;Length(Signal))="#"))

I have thousands of rows. Most rows with Y under Signal Column but their corresponding accounting entry is # under same Signal Column. I need a formula to help me filter both Y and # row with same No.

So for every Y with a #, they have same No which can be used to identify them. Help!! Please

See my 2 columns below. The Db and Cr is just to show more info.

Nos. Signal

10. Y - Db
10. # - Cr
22. # - Db
22. # - Cr
24. Y - Db
24. # - Cr
35. # - Db
35. # - Cr
36. # - Db
36. # - Cr
37. Y - Db
37. # - Cr

This is what I want left

Nos. Signal

22. # - Db
22. # - Cr
35. # - Db
35. # - Cr
36. # - Db

36. # - Cr

##### Add comment
10|10000 characters needed characters exceeded
• Nov 10, 2016 at 10:06 AM

Follow these steps.

Create V Nos=Substr([Nos];1;2) (This will give you the first two values like 10,22 etc)

Var1=If(Match([Nos];"*Y*")) In ([V Nos]) Then "Hide" Else "Show"

Var2=If(IsError([Var1])) Then "Hide" Else "Show"

Apply filter on Var2 where equal to Show and see.

##### Add comment
10|10000 characters needed characters exceeded
• Nov 11, 2016 at 06:54 AM

Only the Y has "Show' using your variable on my end, all other rows with # has "Hide so unable to take corresonding entries out.

1. Can u help me understand why 3 variables?

Also using your variables all rows with Y come up with "hide "while other # has Show and when l try to apply filter l only see Show as the only value from list to filter from even though l could see Hide and Show in report body.

Not sure how you get this on your end.

I really need help with this. Thanks

##### Add comment
10|10000 characters needed characters exceeded
• can you please share the screenshot of your webi report?

what you want to show? my formula will show the values if all values are # for each number.Otherwise it will hide if one Y will come for number.

V Nos=Substr([Nos];1;2) (This will give you the first two values like 10,22 etc)

(This i have used to get the number only so that i can use in the IN context.)

Var1=If(Match([Nos];"*Y*")) In ([V Nos]) Then "Hide" Else "Show"

(I am checking here if any Y for each number then hide else show.)

Var2=If(IsError([Var1])) Then "Hide" Else "Show"

(This i have used because i got the multivalue error where value is Hide.)

• Nov 11, 2016 at 01:43 PM

Things to note on my end Amit. One column is Nos like below which may not necessarily be 2 digit some 3 or more. Another column have the dr and cr indicator which help tie them out to one Nos. Sample columns below:

Nos

10
10
22
22
24
24
35
35
36
36
37
37

Signal Column: I used concatenate funtion to tie the Y or # to Nos

Signal

10Y
10#
22#
22#
24#
24#
35#
35#
36#
36#
37Y
37Y

Then l have another column for dr and cr indicator. Note: for every debit there is credit side, however there could be more than 2 no for each dr and cr side e.g

10 Cr

10 Cr

10 Cr

10 Db

10 Db

The only issue with the above is that for each Nos like 10, there may just be one Y signal while others may be # but the Nos help me tie each group as one so l can then filter out all Nos like 10 with one or 2 Y where they have other #. I will share screenshot shortly.

Lastly l just used your variable below - tried the steps too though

Var1=If(Match([Signal];"*Y*")) In ([Nos]) Then "Hide" Else "Show"

With above, I get Hide where Y only and not corresponding # with same No, l didnt get multivalue error and surprise l only see Hide as the only option from filter section so l just used Not In List of Hide. This works but l need the # with Y nos out too.

Thanks so much for assisting. Appreciate thus greatly.

##### Add comment
10|10000 characters needed characters exceeded
• See attached screenshot.

Also please share your report screenshot.

untitled.png (15.9 kB)
• Nov 11, 2016 at 04:40 PM

Hello Amit - See attached.

One shows the Report while the other shows the Filter page.

I used your formula steps and see what I get in Screen attched

From attached, There's No, Nos, Signal, V.2, and V.3 Fields. What I want left from the Nos Field is row with 350007474 - which also comes up "Hide" under V.3

Note:

1. Some of my Nos do not have same digits (they could be one, two or more less than others)
2. I concatenate Nos with Key to get Signal and No with another Field to get Nos - Screen shot should help explain what I mean here
3. Filter Page only shows Hide option and not Show too - any reason why? But when I used not in List of Hide it does recognize it though

Thanks for your help on this.

sap.png

##### Add comment
10|10000 characters needed characters exceeded
• use below formula on Key.

Var=Max([Key]) In ([Nos])

Apply filter on the var where equal to #.

Key part in your formula is get the Hide or X in all rows for Nos where X is at-least in the NOS.This you can do with IN context.

• Nov 14, 2016 at 02:06 PM

Thank You So much Amit. Works! Works!! Works!!!.

##### Add comment
10|10000 characters needed characters exceeded