Filter Rows in Magic ETL based on Text

Shevy
Shevy Contributor
in Archive
I am using the Filter Rows aspect of Magic ETL and having trouble filtering if the text filed ins not one word. For example if I create a filter that says ColumnA equals TESTWORD - I get rows where the columns had the value TESTWORD. If I create a filter that tries to filter ColumnA equals TEST WORD - (space between test and word)the filter does not work Even if I change the filter to be ColumnA Starts with TEST - the filter does not return rows where it is TEST WORD Thoughts?
Dojo Community Member
** Please like responses by clicking on the thumbs up
** Please Accept / check the answer that solved your problem / answered your question.
Tagged:

Comments

  • kshah008
    kshah008 Contributor

    Hi all,


    Can anybody help @Shevy with their situation?

    Thanks!

  • Gimli
    Gimli Domo Employee

    Hello @Shevy, 

    This would be a great thing to ask the DOMO Support Team. You can reach them at the following url https://support.domo.com/

    **Say “Thanks" by clicking the thumbs up in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • Starlord
    Starlord Domo Employee

    Hello @Shevy,

    As a reminder, I contacted you via email asking for a link to your dataflow so that I may help troubleshoot this issue for you.

    When you're ready to proceed, please reply to my email, and I can help you then.

    Thank you!

    Steve Cook

    Domo Support

    Starlord
    **Say “Thanks” by clicking the “heart” in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • cbishop
    cbishop Member

    I am having a similar issue. I want to filter rows *only* when their value is specific (Labor). I have used "Labor", 'Labor', and Labor and each itteration nets 0 records.  Please advise. 

  • DMJerry
    DMJerry Member

    Anyone have an answer for this?

  • Asydnor
    Asydnor Member
    I have found that filtering on TEXT fields in ETL Data Flows is unreliable. My work around is to use the VALUE MAPPER to create a NEW COLUMN with a NUMBER in it for those text fields I want to filter on. I then use COLUMN TYPE to set the column type to WHOLE NUMBER (it defaulted to a text field when I created the new column in Value Mapper.) Then I FILTER to include only those rows with the number I identified in the new column created in Value Mapper. Additionally, I have found one other unreliable responses with FILTER: Multiple filter values in one FILTER sometimes don't work, so I do one FILTER step for each field I want to Filter on. I discovered all this attempting to create a MaxDate field. I also found in the GROUP BY feature using large file with many rows, the process stopped after processing only part of the file. So I added a filter to further reduce my row count to get an accurate result for MaxDate.
  • Starlord
    Starlord Domo Employee

    @Asydnor Have you reported these behaviors in tickets to Domo Support? I would very much like to have examples of these issues you see brought to our attention.

     

    If you have open tickets, please reference them here for me. You should have been provided a CASE ID for each ticket opened.

     

    If you do not have open tickets, please do open some via the Support portal here: support.domo.com 

     

    If at all possible, please provide examples for us of where you see this.

     

    Thank you!

    Starlord
    **Say “Thanks” by clicking the “heart” in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • jreschlien
    jreschlien Member

    Hi All, 

     

    I am having the same issue. When applying multiple filters for a TEXT field, it doesn't seem to work. If I have one rule, it works fine, but after adding multiple, it doesn't work at all. Thanks!

     

  • Ashleigh
    Ashleigh Coach

    I personally never use the equals but always contains and does not contains. Have you tried going to the dataset and copying the text and then pasting that in the filter field? Sometimes there are whitespaces there and just typing them in does not catch them. Also, make sure your column is actually a text field by setting the column type. I have never had any issues with filtering text fields so if it persists I would definitely put in a ticket. 

    **If this answer solved your problem be sure to like it and accept it as a solution!

  • user095063
    user095063 Member

    Experiencing the same issue. When applying multiple filters in Magic ETL, it returns 0 results. Even though I see the raw data and it's clearly there, however, can't use filters to get the sample of the data.

  • Starlord
    Starlord Domo Employee

    Hello @user095063,

     

    I work in Domo Support, and can provide some help here.

     

    Very often, with this type of issue, it'll turn out that something weird is happening with whitespace. Examples I run into a lot are:

    • Spaces in values are actually double-spaces or tabs (this can be hard to find because most web browsers automatically "correct" double-spaces to single-spaces, so you can't see the issue in the data preview)
    • There are unexpected spaces, tabs, or line-breaks at the start or end of values

     

    I advise you review your data to see if any of these issues exist, along with simple spelling errors in the data that aren't in your filter value.

     

    If you can't find the cause of the problem, please go to support.domo.com and submit a support ticket. Please indicate the dataflow you're working with, which filter tile/s you're looking at, and which value/s in your data you expect to make it through the filter tile that are not making it through.

     

    Thank you!

    Starlord
    **Say “Thanks” by clicking the “heart” in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • Sell_the_Wings
    Sell_the_Wings Member

    Solved (for me):

     

    Filtering using 'does not contain' on a simple text string.  I got it to work for a single filter on my first try, but as I tried to add other filters it stopped working. The Run Preview process just refused to do anything once it got to the Filter Row step.

     

    Then I stumbled upon what I think was the problem for me, and probably others.  After you type in your rule, there is a big button labeled "Add Filter Rule". I should be forgiven for thinking that is what you are supposed to hit to complete the process of adding the rule just above. But what it seems to do is actually open up a new rule dialogue box, and if that dialogue box remains open, the process fails. Once I closed that empty dialogue box by hitting the large "X" to the right, everything processed fine.

    I have replicated this problem, and added several more rules and it all works great.

Categories