How to Group Dynamic Columns (date based)?

[Uri_Teitler]

We run a report every week to look for missing timesheets.

We need to group them by Prior Period and Current Period.

Then determine if there is any missing in those periods.

The report creates a new col each week (week beginning).

Once all missing timesheets are submitted the oldest dates drop off.

Attached is a sample of the input file and expected output file.

For this example I have assumed the "today's date" ie current period is 13/09/21 (DD/MM/YY)

Any help would be much appreciated.

[atcodedog05]

Hi @Uri_Teitler 

Here is how you can do it. It captures Latest 2 dates and sets it as current & prior.

Workflow:

Hope this helps : )

[Maskell_Rascal]

Hi @Uri_Teitler 

Here is a fun way to do this. I've been getting into the Developer tools a lot recently, so this solution uses several of them to dynamically grab columns and rename them for you. 

I'm getting a list of the field names, then grabbing the last two and first field names, and using these to update the metadata description fields for these columns. The Dynamic Select tool now only keeps fields where the metadata description field is not empty. I then use a Multi-Field formula tool to update all fields with the exception of "Resource Name". And then finally use another Dynamic Rename to update the field names to Prior and Current Period to match your output. 

Let me know if this works for you. 

Cheers!

Phil

[atcodedog05]

I am really amazed by your approach @Maskell_Rascal 😀

Its definitely a different way to look at things 🙂

[Maskell_Rascal]

Thanks @atcodedog05!

I started to experiment with this approach out of necessity. There are times when you will find yourself working with a very large dataset and that transposing the data drastically decreases performance.

[atcodedog05]

@Maskell_Rascal 

Interesting 🤔 definitely a valid point and Amazing 🙂 how you are able to tackle it. Looking forward to exploring and learning a lot more from you 😀

A suggestion you can turn dynamic rename warning messages by toggling below. Some people are bothered by warning messages 😅 this it result window would look clean. And doesn't unnecessarily make you worry.

Hope this helps : )

[Maskell_Rascal]

@atcodedog05 I might write up a post on this at some point, but the applications to this method are pretty robust depending on how creative you get with it. 😁

I'm currently using it similar to the solution I posted, though I've also used it to dynamically start my import of data files that fluctuate the starting row/columns. 

I also just wanted to post quick the performance improvements between the transpose and dynamic select methods. For this experiment, I copied the sample data down to a little over 1 million rows. The transpose method ran in 1:15 minutes vs 10.3 seconds with the dynamic select method! 

Transpose method:

Dynamic Select method:

[atcodedog05]

Hi @Maskell_Rascal 

I did a similar performance test to check after seeing your solution 😅. I was totally amazed by the performance boost of dynamic rename and dynamic select method. I have used these steps before in bits and pieces. But never had the necessity for any usecase or thought of building a powerful solution like this 🙂. It's definitely a creative solution for sure 🙂.

Its is truly amazing and great learning 🙂

[Uri_Teitler]

Firstly, wow .. I have to admit the performance of the query was amazing.

Unfortunately when looking at a larger data set it would appear we have some strange employee behaviour (surprise surprise)

The second last col is not always a predictor of previous cols.

Ie you might be missing one from early august but complete them in the last few weeks.

I still need to call out that prior period is missing.

In excel I did a COUNTA(First Period : Second Last Period) to see if there was anything in any prior period.

Is there something that could do that check first before joining back?

[atcodedog05]

Hi @Uri_Teitler 

You can check with the transpose method once too. Since i am sorting dates.

Can you provide some sample input and expected output it will help us get a better understanding of the usecase.