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

  • Get RSS Feed

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

  • 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

  • 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

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