Hi,
I have a specific condition that I'm struggling with and wondering if anyone is able to help.
I have a set of input data as below:
(I have a bunch of date in that Date field (repeating multiple times) and ID field that also repeats multiple times
Project ID | Date | Status |
:00027408 | 11/7/2019 | |
:00027408 | 11/14/2019 | |
:00027408 | 11/21/2019 | |
:00027408 | 11/28/2019 | |
:00027408 | 12/5/2019 | Not Rated |
:00027408 | 12/12/2019 | Not Rated |
:00027408 | 12/19/2019 | Not Rated |
:00027408 | 1/9/2020 | Not Rated |
:00035276 | 11/7/2019 | Not Rated |
:00035276 | 11/14/2019 | Not Rated |
:00035276 | 11/21/2019 | Not Rated |
:00035276 | 11/28/2019 | Not Rated |
:00035276 | 12/5/2019 | Not Rated |
:00035276 | 12/12/2019 | Not Rated |
:00035276 | 12/19/2019 | Not Rated |
:00035276 | 1/9/2020 | Not Rated |
:00037253 | 11/7/2019 | Not Rated |
:00037253 | 11/14/2019 | Not Rated |
:00037253 | 11/21/2019 | Not Rated |
:00037253 | 11/28/2019 | Not Rated |
:00037253 | 12/5/2019 | Not Rated |
:00037253 | 12/12/2019 | Not Rated |
:00037253 | 12/19/2019 | Not Rated |
:00037253 | 1/9/2020 | Not Rated |
:00037263 | 11/7/2019 | Not Rated |
:00037263 | 11/14/2019 | Not Rated |
:00037263 | 11/21/2019 | Not Rated |
:00037263 | 11/28/2019 | Not Rated |
:00037263 | 12/5/2019 | Not Rated |
:00037263 | 12/12/2019 | Not Rated |
:00037263 | 12/19/2019 | Not Rated |
:00037263 | 1/9/2020 | Not Rated |
:00038804 | 11/7/2019 | Yellow |
:00038804 | 11/14/2019 | Yellow |
:00038804 | 11/21/2019 | |
:00038804 | 11/28/2019 | Yellow |
:00038804 | 12/5/2019 | Yellow |
:00038804 | 12/12/2019 | Yellow |
:00038804 | 12/19/2019 | Yellow |
:00038804 | 1/9/2020 | Yellow |
:00039836 | 11/7/2019 | |
:00039836 | 11/14/2019 | Not Rated |
:00039836 | 11/21/2019 | Not Rated |
:00039836 | 11/28/2019 | Not Rated |
:00039836 | 12/5/2019 | Not Rated |
:00039836 | 12/12/2019 | Not Rated |
:00039836 | 12/19/2019 | Not Rated |
:00039836 | 1/9/2020 | Not Rated |
I initially had to use transpose to get the date field turn into their own columns and Status field as their records (which Im able to do successfully)
then i was asked to also count the number of occurance/frequency of the "Yellow" from that status field. (Only when there's Yellow in consetutive rows and no other values appear in between, if there is (like my example below, then it will start counting for Yellow again)
I need to also figure out how many times that Yellow was repeated for the particular ID in the particular date.
Below is the expected results:
Project ID | W | 11/7/2019 | 11/14/2019 | 11/21/2019 | 11/28/2019 | 12/5/2019 | 12/12/2019 | 12/19/2019 | 1/9/2020 |
:00027408 | 0 | Not Rated | Not Rated | Not Rated | Not Rated | ||||
:00035276 | 0 | Not Rated | Not Rated | Not Rated | Not Rated | Not Rated | Not Rated | Not Rated | Not Rated |
:00037253 | 0 | Not Rated | Not Rated | Not Rated | Not Rated | Not Rated | Not Rated | Not Rated | Not Rated |
:00037263 | 0 | Not Rated | Not Rated | Not Rated | Not Rated | Not Rated | Not Rated | Not Rated | Not Rated |
:00038804 | 5 | Yellow | Yellow | Yellow | Yellow | Yellow | Yellow | Yellow | |
:00039836 | 0 | Not Rated | Not Rated | Not Rated | Not Rated | Not Rated | Not Rated | Not Rated |
you will see in the example for ID 00038804 that Yellow counts as 5 rather than 7 because there was a blank (or could be any other values) in between it.
Please let me know if my explanation is confusing.
Thanks in advance!
Solved! Go to Solution.
Is it okay I dm you?
I have posted my sample data here as well.
Maybe the data I have posted before works a bit differently?
Hi @hal_dal, give this version a try. It is the same as previous, just that I sort my universe upfront to group all dates for a given ID, and then use the multi-row formula tool to compute the output.
Thank you so much! @AbhilashR
Just one other thing though,
How would I take into account where the later fields may not be Yellow?
and what I mean by that is:
example above, the count is still 0 because the last field is Green and not Yellow
Sorry, I don't quite follow the logic for the row you highlighted in yellow. I would imagine the count would be 3 since I see three consecutive Yellows from the right.
I would only count those "Yellows" where that row of Project ID absolute ends in Yellow and nothing else.
Basically,
I need to count the consecutive "Yellows" but that's only if there's no other available fields/blank before and after it. (when we look at it across the columns for that particular ID)
Sorry for the confusion, I have just been introduced to this concept and I'm confused myself as well.
No problem at all. Please feel comfortable asking as many questions as you need to get to your destination. I have modified my solution to accommodate for this new piece of information (assuming I am not confused).
It assumes you need Yellow count for rows whose latest status value is Yellow, and not anything else. Below is a summary of only those rows that have Yellow count (the workflow will give you the entire Univ.)
Take a look and let us know.
@AbhilashR thank you so much!
Do you have any suggestions of how I can handle a situation where
The first column has values but the last may not?
Where in my example, I had Yellow for the first column and the rest are all blank and the yelloe was still counted.
How can I fill in that gap for situations where those columns may not have any values (i.e. they were not passed in the first place from the input)
In other words, if a particular ID has only one value coming in, you want to artificially introduce new dates to ensure every ID has the same number of dates (i.e. rows) in them? If yes, then you could explore using the Generate Rows tool. Identify Min and Max date of your universe, which basically gives you the lower and upper bounds in your data. Then use these in the Generate Row tool.