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.
Hi @MarqueeCrew
Thanks for your solution!
This is getting me one step closer to what I need but missing one piece (which is the piece I'm struggling with for a while)
I already implemented what you told me but the thing missing is that,
in the above snippet, where highlighted blue, it's showing me that count is "14" but really, it should be"2"
The reason it should be 2 because, if we start from the left, I need to count how many Yellow there are all the way to the end where there's no other fields in between it.
The 9 yellows from the left basically become useless because there's a Green, then the other 3 Yellow also become useless until we meet the very end where there's "2" Yellow.
Edit: to better word what I mean,
so starting from the right side of my column (or last row before transpose), I want to count the # of consecutive Yellow and just stop counting from there.
Hopefully that clarifies what I meant better.
Thanks
Hi @hal_dal, looking at the explanation in your Edit, this updated approach might serve you better. Give it a try and let us know if it addresses your ask.
@AbhilashR Thanks a lot! The screenshot looks like what I need for sure.
I downloaded your workflow to run and it seemed to run fine but as soon as I connect it to my input file (made sure to change the names to match yours"
The "W" field is null right after the union tool 😞 it's not null before the join (after summarize) though.
Not sure if I should have changed anything else?
Apologize, so it wasn't actually all Null but some data appeared after I exported it.
However, my output seems incorrect.
As you can see below, highlighted in yellow (Only difference I have btw. your workflow and mine is my input data)
First one, I should expect W to be "1"
Second should have been "6"
Third should be "4"
Fourth is fine
and Last should be 5
Not sure what I may have done wrong? 😞
Hi @hal_dal, can you give the attached solution a try on your larger dataset? I created a dummy scenario to replicate your source and below is what I get:
Let us know.
Hi @AbhilashR
Unfortunately that still did not work for me
Running yours seem to work just fine but as soon as I feed mine in (I had to change data type as my date already comes in as date)
But it's still counting any row with "Yellow" as 1 instead of actually counting the correct records.
Can you give me a scenario from your dataset (sample is fine) that my workflow is not able to process?