Missing data

[RMojsiak]

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.

Thank you

[Luke_C]

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]

Hello,

I have attached the sample. Hope it helps.

[apathetichell · Accepted solution]

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]

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

[apathetichell]

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]

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]

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