Alteryx output for reporting

[JohnDw6]

Dear all,

I would like to transform a simple database to more complex output format that will be used for reporting under Excel. 

Please find attached the input and output format. How would you approach such a transformation it in an Alteryx workflow? Do you think it is feasible?

Many thanks in advance for your help,
John

[Amol_Telore]

Hey @JohnDw6 

Here is my solution. I split the workflow into two section. Date and Header. Did table formatting and then merge the data to generated final report.

PFA for reference.

[JohnDw6]

Many thanks Amol! It is an inspiring solution.

Unfortunately, it has been challenging so far to test it on a real dataset with 16k records (workflow has been running for an hour now...)

[Amol_Telore]

@JohnDw6 will you be able to share some sample dataset ? I will see if I can do any performance improvement or find work around.

[JohnDw6]

Thanks for your reply Amol! Unfortunately I am not able to share the dataset...

I stopped the workflow after 5h+ of running and am trying now to split the data by year, and try pivoting it, with hope of a more simple solution. Any help is welcome!

Hereafter I add a simplified version of the input/output format:

Account Level 1	Account Level 2	CC Level 1	CC Level 2	Allocation type	Value
Account group 1	Account A	CC group 1	CC A	Type A	$10
Account group 1	Account B	CC group 1	CC B	Type A	$25
Account group 2	Account C	CC group 2	CC C	Type B	$100
Account group 2	Account D	CC group 2	CC D	Type A	$75

[JohnDw6]

Output would ideally look like: 

	Value	Value	Value	Value	Total
	Allocation Type A	Allocation Type A	Allocation Type A	Allocation Type B	Total
	CC group 1	CC group 2	CC group 2	CC group 2	Total
Account details	CC A	CC B	CC D	CC C	Total
Account group 1	$10	$25			$35
Account A	$10				$10
Account B		$25			$25
Account group 2			$75	$100	$175
Account C				$100	$100
Account D			$75		$75
Total	$10	$25	$75	$100	$210

Thanks!

[Amol_Telore]

Hey @JohnDw6 

There could be various reasons for performance issues. Since I do not have access to actual data, Unfortunately I will not be able to identify the issue and fine tune the process. But from my experience, I suspect below are the three reasons that might be causing the performance issue.

1. Append Field: We are doing cross join in the process. So there is possibility that data is getting duplicated at some node in the workflow if we have not removed duplicates prior doing cross join. Please check this point wherever we are using Append Fields tool.
2. Browse Tool: Disable all browse tools through workflow configuration as it takes time to render all the information and we have used it multiple times in the workflow for testing.
3. Render Tool: Rendering report takes time and depending upon data volume render time may increase. As of now, I do not have any solution for this as it is default behavior of render tool. If your data is not going to change, I mean if header is going to be same all the time then you may create a template file and then write data to that template file using output data tool. This way we can reduce the render time required here.

Hope this helps you to debug the process at some extent. 

Regards,

Amol Telore