Report- Cross tab by month and separate to multiple files by name

@PassION_es Sorry I made the changes in the old macro, not in the new macro. I will make the changes and share with you

@PassION_es I made the changes in the workflow to reflect the text output

@PassION_es Attaching the updated macro

All comments

@PassION_es One way of doing this is with the batch macro. You need to pass the STAFF details in a separate file

Report_0909.yxzp

Hello @binu_acs . Your workflow runs fine however I just have some small findings and request for modification.

Here are the following:

1.) It did not have a "Total" Column as you can see it the snapshot output.

2.) The date in the filename should always be the last reporting month. Correct should be August 31, 2022 because we are currently on September.

3.)In the output, "AKING INA Sample 1", I noticed a Jul 2022 header, instead of Jun 2022. The batch macro output is correct - Jun 2022 (see below screenshot), however the excel output created has a header of Jul 2022.

I would want also to add a title report with "as of date" to be like the first snapshot ( SOA as of August 31 - which is the last reported month). Thank you.

hi @binu_acs , to add on my first query above, I am getting this error when applied to the data i have:

batch macro:

Also, can we sort the order of columns in ascending order date? Nov 2021 is positioned at the end because it starts with letter "N".

@PassION_es updated the workflow

Report_0909.yxzp

Hello @binu_acs , it seems almost all of my queries have been addressed. I tried to enter another set of data in your sample_input but unfortunately, the total inside the output file is not located in the last column: (see highlighted in red)

You can also see that May 2022 which is all in blank column is captured in the file eventhough there is no May 2022 transaction under that STAFF.

Attached is the same Sample_input file with the new data inside.

Sample_input.xlsx

@PassION_es I fixed the issues you mentioned above.

I can see May 2022 has data in the spread sheet (5/5/2022)

Report_0909.yxzp

ANOT PANOT SA IMO HA MANA as of date August 31 2022.xlsx

hello Binuacs. I can see your modified data and each of the output is still capturing the unnecessary dates with all blank rows for some of the date column. I am very sorry for this. For the nth time, can you check on the attached output please? The output files that were generated will be sent out via email to each of the staff (we have hundreds/thousands of staffs in our company) and we want the automation to be presented in the best way we can (formatted table), see sample:

final output (formatting).xlsx

@PassION_es no worries, you can ask any number of times, all am seeing as an opportunity to learn different ways to do things in Alteryx. The batch macro works very similar to the Union tool, that's why you are seeing all the fields in each STAFF even if they don't have any data in a particular month. I am looking for some other option to fix that issue. I will let you know once I found something.

Thank you so much

@PassION_es I decided to create the excel output inside the batch macro, which will remove one issue (blank date field). I am working on the formatting part which you mentioned in your output.

The output files will be created in the same folder where the workflow is saved.

Report_updated_0912.yxzp

Thanks @binu_acs ! I tested it and it indeed generated outpus without those date headers with blank data.

I only have one question. I'm not sure if I modified the Fpath correctly, but I guess not. I wanted the outputs to be saved in the Alteryx shared drive, but they are still reflecting in my Downloads (external) folder. Here's a snapshot of the formula to which I changed Fpath inside the batch macro:

I also saved a copy of same input file to the Alteryx shared drive and changed the path in the macro input but still outputs are saved directly to the "Downloads" folder:

@PassION_es I think it is because of the output tool configuration of the default file path highlighted below. Can you also update the highlighted path with your original path?

Hi @binu_acs , I also changed the output file configuration tool yesterday:

I guess the error was due to the crosstab tool causing the fpath and others in the select tool to be missing: (please note that below is an error when running the batch macro -.yxmc, but no error encountered when running the yxmd). However, the fpath is still in my download folder, not on the location i specified in the output confirguation tool.)

Missing in the Select tool:

Please note that I am still using the same dummy data and the same file.

@PassION_es I am able to run with the same dummy files without any issues. I am attaching the package again. Can you try to run this package ? There was an error in the cross tab which is why the select fields showed an error. I updated my fpath to a new location and seems to be working for me.

Report_updated_0912.yxzp

hi @binu_acs , Its very weird. The outputs are still mapped to your your location even though I already modified the input and output configuration tool to Alteryx shared drive location or even to my local drive:

Is this something to do with workflow dependencies under Advance options?

@PassION_es I am not sure why the error. Can you create a new workflow and the macro very similar to the one I created and try from your side?

@PassION_es Also as you mentioned above can you try to change the advanced option for workflow dependencies

gotcha. @binu_acs The outputs are now saved to my location. I just went to the Advanced options-> click the workflow Dependencies -> and edited all the path from your drive to mine. I also clicked "ALL UNC", so it won't be dependent on any externals. I saved also both yxmd and yxmc (updated) to the local folder and when I hit run, there are no error encountered. Thank you so much It fixed the issue now.

@PassION_es ohh great !!

@PassION_es To get the output in the proper format I think you need to add the output in a render tool. Can you try to connect a table tool and a render tool inside the macro just before the last Select tool? My Alteryx terminates when I connect the select tool output to the Table tool. The configuration should be like the below. I just wanted to see it is working for you or not

Table tool configuration

Render tool configuration

@binu_acs , i'm not getting error on the workflow, however the output has no date append in the filename and the data iniside it includes FPATh column, though I already deselect it in the table too. And the total is also located in the middle of the report.

Please generated outputs for your reference. Kindly note that I removed the output file because it generated the same sets of output without formatting, but with dates in the filename:

I also tried to modify filename in the render tool from Append to Replace filename/entire path with group but it generated the same output filename and data. Maybe there's a need to be fixed in the prior tools to fix the basic and render tool.

testANOT PANOT SA IMO HA MANA .xlsx

testPASSIONES IKAW LANG.xlsx

testTotal.xlsx

PASSIONES IKAW LANG as of date August 31 2022.xlsx

In addition @binu_acs , although I disable the container containing the output tool, still it generated the same set of outputs with dates. So the workflow after running generated two sets of data, one maybe came from the ouput (no formatting), and the other came from render tool.

Please see attached output file with no formatting.

ANOT PANOT SA IMO HA MANA as of date August 31 2022.xlsx

@PassION_es If the render tool is able to generate the formatted output I think you can delete the output tool from the macro itself. Can you create a new field called sheet name and update like below

then give the original path in the render tool configuration like the below screenshot

and try to run, it should create two separate files in your specified path

let me know how it goes.

@binu_acs , its kinda weird. Right now when i edited the formula tool and add the filaname field, but they no longer recognizable by alteryx. See snapshots with blank results:

After running, no outputs generated due to below error:

Attached is the updated yxmc after applying the instructions you've provided. (Note I change the table configuration from Group by staff to filename).

soa.yxmc

@PassION_es I made a few changes in the workflow. Can you try the attached workflow

ANOT PANOT SA IMO HA MANA as of date August 31 2022.xlsx

Hi @binu_acs . The changes generated only one output (the last staff). No output generated for the first staff. Total is still in the middle of the report.

@PassION_es Sorry I made the changes in the old macro, not in the new macro. I will make the changes and share with you

No worries @binu_acs . Take your time. I just also want to thank you for being so helpful.

@PassION_es I updated the workflow with the table tool and render tool. Attaching the workflow