The steps above allow for you to create a new column in your DataSet that contains the identical data contained in the original "Department" column. Select your "Department Store" DataSet as the input DataSet in Magic ETL.Īdd the Value Mapper action (under Edit Data) and connect it to the input DataSet. Now that you have an EXACT word ( TRUE) for any cell that contains "men" or "Men," you can filter them away.Īdd a Filter action and connect the Replace Text action to it.įor step 1, choose the "FormattedDepartment" column. The total RegEX says, "IF the Column contains 'men' or 'Men' THEN replace the ENTIRE CELL with the word TRUE." Men|Men means the word "men" in all lowercase OR "Men" with a capital M. * means all characters from left to right. The steps above allow you to use a Regular Expression in Magic to determine if the "FormattedDepartment" column contains the word "men" or "Men." In the settings icon of step 2, choose Use RegEx. In step 1 add the "FormattedDepartment" column created above. We want this so we do not affect the raw data, only the new data we create.Īdd a Replace Text action (under the Edit Data section on the left) and connect the Value Mapper action to it. The steps above allow you to create a new column in your DataSet that will contain the identical data contained in the original "Department" column.
In step 5 select the settings icon and choose Empty String. In step 4 select the settings icon and choose Empty String. In step 3 select Keep the original value. Select your Department Store DataSet as the Input DataSet in Magic ETL.Īdd the Value Mapper action (under Edit Data on the left) and connect it to the input DataSet.Īdd the "Department" column as the column to search.Ĭhoose Write to a new column and name the new column "FormattedDepartment." To implement DOES NOT CONTAIN using this method, This method uses Regular Expressions to implement DOES NOT CONTAIN.
For more information about this session, see Tips and Tricks: Quick Lessons from Power Users. The content in this article was shared during our Domopalooza 2017 Tips and Tricks session. The client is not an SQL expert and would like to use Magic ETL to accomplish this task. The data is often skewed in the Men’s and Women’s departments, so these departments need to be filtered out of the data. Fortunately there are two ways to implement DOES NOT CONTAIN in Magic ETL: a simple but technical way, and a long but non-technical way.įor both of these examples, we assume the client is over a department store that contains several departments. Ideally there should be a relatively simply drag-and-drop tile in Magic ETL that includes DOES NOT CONTAIN or a simple procedure to accomplish the same functionality. In a normal situation, Magic ETL does not have a DOES NOT CONTAIN feature built inside of the tool, so you would have to resort to a more technical query engine like an SQL DataFlow or Beast Mode. The problem I described is something I encounter at the workplace and I am mostly restricted to using the ArcMap GUI there.Let's say you need to add a filter for a column that DOES NOT CONTAIN a certain word or characters inside of Magic ETL. I'm interested in any solutions that are more efficient than my final workaround and don't make use of ArcPy scripts. However it still took a while to list every thing I wanted. This was more efficient than something like "Attribute" 'Poly-Line-1' AND "Attribute" 'Poly-Line-2' AND. As a workaround I ended up using a query similar to "Attribute" NOT IN ('Poly-Line-1', 'Poly-Line-2', 'Poly-Line-3', etc) I then tried: "Attribute" 'Poly%'Īgain, it was treated as if the syntax was correct, but no change took place. Unfortunately the file was not affected and each line was drawn as it had been before. I was able to apply this statement, which seemed to indicate that it was syntactically correct. The query I attempted to use was the following: "Attribute" 'Poly-Line-%' Some lines were "Poly-Line-1", some were "Poly-Line-3", "Poly-Line-A", so on and so forth. I noticed that most of the CAD lines I did not want to display shared similarly named attributes. I do not need many of the lines from the. I have a large CAD drawing that I have imported into my map.
I'm attempting to use a definition query in order to display only a subset of data.
0 kommentar(er)
