8 Replies Latest reply: May 15, 2014 10:04 AM by Grant Perkins RSS

    Filter Question

    crazzi _

      I know that this will end up being something simple but I can't seem to see it. I have a text report that consists of data that belongs to us as well as others. I'm using an external lookup to three tables in an Access database to tag ours by matching on account number and pulling specific data.

      I would like to filter on some of the columns from the external look up to pull out all the blanks which I've tried using an expression of Column A >0 but I receive this message in Test tab (Test Value "" cannot be converted into the specified data type). The data type is numeric listed in test tab which is correct so what am I missing??? I've tried other expressions but still get the same error.

      I want to create a few filters on specific columns so that I can create a compound filter to pull only data that would be ours.

      Thanks for your help.....

        • Filter Question
          crazzi _

          Told you...I knew I should have waited a few more minutes....

          Thanks

            • Filter Question
              Data Kruncher

              I take that you've resolved you problem? If so, what was your solution?

                • Filter Question
                  crazzi _

                  Actually when I hit the ok option instead of testing it the data was there so not sure why I received the message. I have changed the filter expression to use <> instead of > because I forgot about the credit balances. I created a compound filter based on three individual formula based filters. Just a question about the results on my compound filter. When I run each filter individually I receive so many records for each and I would expect (which I could be wrong) that I would get the total records for each of these filters when I do the compound filter but I didn't. The column I'm using for the individual filters is from an external lookup and some of the records could potentially have data in each of the individual filters. I'm using the "or" option in the compound filter so am I missing something about my total record count???

                  Thanks

                    • Filter Question
                      Grant Perkins

                      If your total record after filter is less than any of the individual filters I would think, if I have understood things correctly, that might not be so good.

                       

                      However, if some, perhaps many, records could be selected by more than one filter then the total return would not be the sum of the individual filters.

                       

                      I would be tempted to rund a compound filter extract and export it to something simple - you don't need many fields, just enough to make the filters work - and then run each of the individual filter against that new extracted list. Compare the counts for the individual filters between the extracted version and the original data. If they don't match ... you may have a problem.

                       

                      Also if the combined extraction is a very large number of records - very very large - you may need to be extra careful ESPECIALLY if you have a high number of fields in total. We are probably talking in the millions here so you may be able to rule out any problems without too much consideration but I thought I would mention it just in case.

                       

                      HTH.

                       

                       

                      Grant

                        • Filter Question
                          crazzi _

                          I exported out the three individual filter table reports and combined into one report in which I then removed the duplicates. I exported out the compound filter table and compared these two files and I didn't have any without a match. Would you suggest doing this test again on a different set of reports or can I be save in saying that I should get all the data I need when running the compound filter only???

                          The issue is that I am using a text document of cash transactions posted on a specific day but only want to pull out the transactions that belong to us so this is where the external  lookup comes into play to compare the cash report to an Access database that is a warehouse for our accounts.

                          Thanks for your help.

                            • Filter Question
                              Grant Perkins

                              Well, it sounds like the compound filter is working as expected (always comforting to check though, just in case).

                               

                              Unless you think you may end up dealing with several million records (pre-filter) or ther might be any other issues that could produce a problem (I can't think what) you would seem to be safe.

                               

                              If you have a key fields combination that is your base line you should be able to come up with a fairly easy check for the total number of records using a summary and then seeing how many lines you get with unique keys - no need to remove duplicates I think.

                               

                              If the individual filters work I don't see any reason for the compound filter to fail and I don;t think any such apparent failure has been reported here, so things should be safe enough.

                               

                              I see no jharm in running the test again  once or twice with different reports if it helps to create a test record that can be referred to should anyone audit the process. That would be good business practice.

                               

                               

                              Grant

                                • Filter Question
                                  crazzi _

                                  Thanks Grant....

                                  Can you tell me if you think that the expression that I have chosen is the best solution to capture both debt and credit transactions in a specific column that contains zeros and blanks in the individual filters??

                                  <>0

                                  What I'm trying to do is capture any account with a dollar figure in Account Balance and not a blank in that field.

                                  Thanks again.....

                                    • Filter Question
                                      Grant Perkins

                                      Thanks Grant....

                                      Can you tell me if you think that the expression that I have chosen is the best solution to capture both debt and credit transactions in a specific column that contains zeros and blanks in the individual filters??

                                      <>0

                                      What I'm trying to do is capture any account with a dollar figure in Account Balance and not a blank in that field.

                                      Thanks again.....[/quote]

                                       

                                      It depends on whether a 0.00 balance is a valid value for reporting. That expression wouold exclude any 0.00 values.

                                       

                                      Also it would work for a numeric field (as extracted) but (if you have defined the field as numeric) the 0 should work to exclude that record a blank in the input field would present a null value in a numeric field type. If you have not pre-handled this by using a calculated field to ensure nulls become zeros (assuming it is safe to take that course!) then you will probably also need to filter for NULL as well, most likely using the ISNULL() function in the expression.  There is a filter example in the ISNULL() function help.

                                       

                                      You can see if you have the probem by running ISNULL(FieldName)=1 to report any NULL values.

                                       

                                      ISNULL(FieldName)=0 will include only records that are NOT null.

                                       

                                      The Compound Filter concept is there to help you build the expressions of course! It also means that you have fewer base filters to create.

                                       

                                      HTH.

                                       

                                       

                                      Grant