This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
I have a record set where Column A contains a date in YYYY-MM-DD format, Column B contains a unique identifier, and Column C contains a string with several eight-digit values separated by an unusual delimiter. What I'd like to do is create a new column (actually, I want to create several but for the purposes of this thread I'll assume whatever process is executed for one new column can be executed for the rest) that returns a value of "1" in the cell if Column C contained a certain value.
(Note: I'd like to avoid using Text-to-Columns because Column C could include dozens of values, so it'd get pretty unwieldy).
In the table above, there are four unique IDs across two days. The three unique IDs present on 2019-03-28 have the '01010101' value, while the single unique ID present on 2019-03-27 does not. My hope is to use the SUMMARIZE tool to group by date, then count the number of unique IDs, and count the total number of rows in which the '01010101' value was present. The end result should transform to this:
Count of '01010101'
Any help you can provide would be deeply appreciated!
@essemMLB Hi, I will recommend to create a new field that flags if the records contains "01010101". Then Group by into two sets ( one just count the distinct number of ID, and another one only count the numbers of records where the flag shows "Y" - use a "filter" before grouping) ..
Then, join the two groups by date. For the items that with no "01010101" contained, it might not show any result, so after the joining, you will also need to "Union" the data from both 'Left" and "Joined" results.
In the end, I applied the data cleaning (which replaces the null value by 0). Please see the sample workflow I attached here. Hope this is helpful!