Advent of Code is back! Unwrap daily challenges to sharpen your Alteryx skills and earn badges along the way! Learn more now.

Alteryx Designer Desktop Discussions

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

Creating a Yearly Log File

clarrock
8 - Asteroid

Hi

 

Here are two sample alteryx outputs for Monday and Friday

 

Alteryx Output for 4th September 2020 - 

DATEITEM IDCOLOUR
2020-09-0468Red
2020-09-0456Red
2020-09-0498Red
2020-09-0432Red
2020-09-0445Blue

 

Alteryx Output for 7th September 2020

DATEITEM IDCOLOUR
2020-09-07687Red
2020-09-07562Red
2020-09-07984Blue
2020-09-07321Blue
2020-09-07456Red

 

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'

DATERED COUNT
2020-08-143
2020-08-211
2020-08-282
2020-09-044

 

Log File.xlsx - Sheet NAme - 'Blue'

DATEBLUE COUNT
2020-08-142
2020-08-214
2020-08-282
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'

DATERED COUNT
2021-01-013

Log File.xlsx  - Sheet Name  - 'Blue'

DATEBLUE COUNT
2021-01-012

 

10 REPLIES 10
grazitti_sapna
17 - Castor

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

Thanks.

Sapna Gupta
atcodedog05
22 - Nova
22 - Nova

Hi @clarrock ,

 

Here is an approach how that can be achieved.

atcodedog05_1-1600410471859.png

 

 

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
8 - Asteroid

@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
22 - Nova
22 - Nova

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
17 - Castor

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

Sapna Gupta
clarrock
8 - Asteroid

@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
17 - Castor

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.

grazitti_sapna_0-1600414649180.png

 

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

 

grazitti_sapna_0-1600414724772.png

 

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.

 

 

 

Sapna Gupta
clarrock
8 - Asteroid

@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

 

IMG_20200918_132738.jpg

grazitti_sapna
17 - Castor

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.

Sapna Gupta
Labels
Top Solution Authors