Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Count # of occurance of field across columns

hal_dal
8 - Asteroid

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 IDDateStatus
:0002740811/7/2019 
:0002740811/14/2019 
:0002740811/21/2019 
:0002740811/28/2019 
:0002740812/5/2019Not Rated
:0002740812/12/2019Not Rated
:0002740812/19/2019Not Rated
:000274081/9/2020Not Rated
:0003527611/7/2019Not Rated
:0003527611/14/2019Not Rated
:0003527611/21/2019Not Rated
:0003527611/28/2019Not Rated
:0003527612/5/2019Not Rated
:0003527612/12/2019Not Rated
:0003527612/19/2019Not Rated
:000352761/9/2020Not Rated
:0003725311/7/2019Not Rated
:0003725311/14/2019Not Rated
:0003725311/21/2019Not Rated
:0003725311/28/2019Not Rated
:0003725312/5/2019Not Rated
:0003725312/12/2019Not Rated
:0003725312/19/2019Not Rated
:000372531/9/2020Not Rated
:0003726311/7/2019Not Rated
:0003726311/14/2019Not Rated
:0003726311/21/2019Not Rated
:0003726311/28/2019Not Rated
:0003726312/5/2019Not Rated
:0003726312/12/2019Not Rated
:0003726312/19/2019Not Rated
:000372631/9/2020Not Rated
:0003880411/7/2019Yellow
:0003880411/14/2019Yellow
:0003880411/21/2019 
:0003880411/28/2019Yellow
:0003880412/5/2019Yellow
:0003880412/12/2019Yellow
:0003880412/19/2019Yellow
:000388041/9/2020Yellow
:0003983611/7/2019 
:0003983611/14/2019Not Rated
:0003983611/21/2019Not Rated
:0003983611/28/2019Not Rated
:0003983612/5/2019Not Rated
:0003983612/12/2019Not Rated
:0003983612/19/2019Not Rated
:000398361/9/2020Not 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 IDW11/7/201911/14/201911/21/201911/28/201912/5/201912/12/201912/19/20191/9/2020
:000274080    Not RatedNot RatedNot RatedNot Rated
:000352760Not RatedNot RatedNot RatedNot RatedNot RatedNot RatedNot RatedNot Rated
:000372530Not RatedNot RatedNot RatedNot RatedNot RatedNot RatedNot RatedNot Rated
:000372630Not RatedNot RatedNot RatedNot RatedNot RatedNot RatedNot RatedNot Rated
:000388045YellowYellow YellowYellowYellowYellowYellow
:000398360 Not RatedNot RatedNot RatedNot RatedNot RatedNot RatedNot 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!

21 REPLIES 21
MarqueeCrew
20 - Arcturus
20 - Arcturus

Hi @hal_dal ,

 

please try:

 

SUMMARIZE:

 

group by [project id]
group by [Status]
count [project id]

FILTER 

 

!IsEmpty([Status])

 

 

cheers,

 

mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
hal_dal
8 - Asteroid

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)

 

hal_dal_0-1588648232364.png

 

 

I already implemented what you told me but the thing missing is that,

 

hal_dal_1-1588648375642.png

 

 

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

AbhilashR
15 - Aurora
15 - Aurora

Hi @hal_dal, below is a screenshot of my solution output. Is this what you are looking to build?

AbhilashR_1-1588649898537.png

 

 

AbhilashR
15 - Aurora
15 - Aurora

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.  

hal_dal
8 - Asteroid

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

hal_dal
8 - Asteroid

@AbhilashR 

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)

hal_dal_0-1588654482697.png

 

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? 😞

AbhilashR
15 - Aurora
15 - Aurora

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:

AbhilashR_0-1588703741209.png

Let us know.

 

hal_dal
8 - Asteroid

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.

AbhilashR
15 - Aurora
15 - Aurora

Can you give me a scenario from your dataset (sample is fine) that my workflow is not able to process?

Labels