Creating a Yearly Log File

[clarrock]

Hi

Here are two sample alteryx outputs for Monday and Friday

Alteryx Output for 4th September 2020 - 

DATE	ITEM ID	COLOUR
2020-09-04	68	Red
2020-09-04	56	Red
2020-09-04	98	Red
2020-09-04	32	Red
2020-09-04	45	Blue

Alteryx Output for 7th September 2020

DATE	ITEM ID	COLOUR
2020-09-07	687	Red
2020-09-07	562	Red
2020-09-07	984	Blue
2020-09-07	321	Blue
2020-09-07	456	Red

The Alteryx Workflow/Output runs from Monday to Friday.

How do I create a log file with different Sheets (Red and Blue) which only takes in counts of Reds and Blues on a Friday run Alteryx Output and not Monday to Thursday Alteryx Outputs as shown in below sample :

Log File.xlsx  - Sheet Name  - 'Red'

DATE	RED COUNT
2020-08-14	3
2020-08-21	1
2020-08-28	2
2020-09-04	4

Log File.xlsx - Sheet NAme - 'Blue'

DATE	BLUE COUNT
2020-08-14	2
2020-08-21	4
2020-08-28	2
2020-09-04	1

Also once the year in the Alteryx Output changes to 2021/2022/etc - the log file should delete all the previous rows/records and start with a fresh row as such

Log File.xlsx  - Sheet Name  - 'Red'

DATE	RED COUNT
2021-01-01	3

Log File.xlsx  - Sheet Name  - 'Blue'

DATE	BLUE COUNT
2021-01-01	2

[grazitti_sapna]

Hi @clarrock , will it be possible to provide a sample dataset that you use as an input?

Thanks.

[atcodedog05]

Hi @clarrock ,

Here is an approach how that can be achieved.

Basically i am maintaining a Database file which has all the historical data that would also be the output file with sheets.

Each time i read new data i append it with old data. It uses a formula to get week day and group it based on Weekday-Colour. These groups are outputted as separated sheets. You can use a filter at later part to keep only data of new year.

Hope this helps. Let me know if you need any help.

Cheers and Happy Analyzing : )

[clarrock]

@grazitti_sapna these are what my current Outputs are in my Alteryx Output (04.09 - Friday run and 07.09 - Monday run). It's a daily file from Monday to Friday

How do I create the attached Required Output file - this file groups by Colour and Counts Colour (on Fridays only) and then creates a record with the date and count in Red and Blue sheet (even if count is 0 for Blue/Red). This file will keep adding new rows until it reaches 2021 where it deletes all previous rows and starts with a fresh row.

Hope this helps, thanks

[atcodedog05]

Hi @clarrock ,

I generated random data and built this approach. Check and let me know if it meets the requirement.

If you want only Friday you can filter only records of Friday

[grazitti_sapna]

@clarrock , I guess I was not clear with the requirement, what I meant was you are providing data for 4.09 and 7.09 whereas your output consist of data of dates before as well, how can I get that data?

Thanks.

[clarrock]

@grazitti_sapna sorry for not being clear. It's just a sample to show that every Friday run (ie 28.08, 21.08, 14.08) Alteryx Output  count is taken into consideration not the Monday runs (eg 07.09).

These are of past Friday runs. I have attached the Alteryx Output for Monday just to show that the count isn't taken in my log file/required output.. For for testing purposes. 

[grazitti_sapna · Accepted solution]

Hi @clarrock , I have tried creating a workflow based on the sample you have provided.

1) Please note that for the filter where Flag =1 is true use this configuration for output file.

2) for the filter where !Isnull(Date) is true use this configuration for output file.

Rest I have also attached a sample file containing 2021 as date where you can connect in order you want to test the second filter.

I hope this helps.

Thanks.

[clarrock]

@grazitti_sapna  tried to open the workflow but I'm getting this error. Can't import the file and can't download the latest version too. Please help

[grazitti_sapna]

Hi @clarrock , I have downgraded the workflow version to 2019.1. If your version is still lower than 2019.1 then open the workflow in notepad and change the version, it will work.

Thanks.