Looking for Starter Kits? Head to the Community Gallery! Now formatted as YXIs for easy installation.

Alteryx Designer Desktop Discussions

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

Missing data

RMojsiak
8 - Asteroid

Hello,

 

I am new to Alteryx and I would need some help with a problem. I have data similar to the table below and I need to find IDs that are active between B-C, but they don't have data in some months (columns E to P). For example, the first ID should be flagged because in March, November, and December 2019 it doesn't have an "x", but it shouldn't flag December 2020 (active until 10.10.2020). Is there a tool/macro that can help me?

I tried with formulas, but tons of "ifs" should be made.

 

RMojsiak_1-1619003411173.png

 

Thank you

7 REPLIES 7
Luke_C
17 - Castor

Hi @RMojsiak 

 

Are you able to post the data itself instead of a screenshot? I have some ideas, I think a transpose and some datetime functions should help get the answer you're looking for. 

RMojsiak
8 - Asteroid

Hello,

 

I have attached the sample. Hope it helps.

apathetichell
18 - Pollux

updated version - added a record id. wasn't sure if you wanted to check for x's until today or have an infinite end for entries without final dates.

RMojsiak
8 - Asteroid

Thank you so much. It's what I wanted😁

apathetichell
18 - Pollux

Thanks - just make sure you have the second version (with record ids) - Having two ids with 1 in a transpose situation can be confusing.

RMojsiak
8 - Asteroid

I modified the formula a bit because it wouldn't flag the months missing from the "toDate" column in previous years. For example: if the ID was active 01.01.2019-10.10.2020, it wouldn't flag 01.11.2019. But again, thank you so much. Your way saved me a lot of time.

apathetichell
18 - Pollux

Try this version - I converted the name/year to full date in a dummy flag and then use date comparisons.

Labels