Count # of occurance of field across columns

[hal_dal]

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!

[hal_dal]

@AbhilashR 

Is it okay I dm you?

[hal_dal]

[hal_dal]

@AbhilashR 

I have posted my sample data here as well.

Maybe the data I have posted before works a bit differently?

[AbhilashR]

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. 

[hal_dal]

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

[AbhilashR]

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. 

[hal_dal]

@AbhilashR 

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.

[AbhilashR]

@hal_dal, 

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.

[hal_dal]

@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)

[AbhilashR]

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.