Need help in data trasnformation

[anonymous008_G]

Hi – I am working on one of my automation projects where I need to automate one monthly report which we do manually. In the report we get mainly 4 columns of data i.e Gross, Net, Long and Short. Gross and Net is always there in the report but Long and Short is something keep changes every month depending on the report. Sometimes reports have long data, sometimes short data and sometimes both. So, I am working on the Alteryx workflow which works in all of these 3 conditions. While developing the flow, I am now stuck at one end, where I am not able think about solution. Can someone from the community help me to solve this further.

So currently I am at Unique block tool of Alteryx, and I want to replace U table null values from long and short column with D table Long and short column values for the respective sector values. If it is null at both table then keep as null.

 I tried joining both tables and then using formula tool with expression replace long value from Right table long and replace short value from right table short but this is getting failed when either Long or Short doesn’t come in the report for any month. So how could I solve this so that even if D table is blank then also my flow would work or in other words if any month only long comes and short doesn't (vise versa) then also flow should work.

thanks in advance. sorry for the large description. 

Unique table:- 

Sector	Gross	Net	Long	Short
Maturity(,1)	29.37	22.17	29.37	0
Maturity[1,3)	55.07	43.8	0	5.64
Maturity[10,)	0.25	0.25	0.25	0
Maturity[3,5)	34.04	21.63	0	6.2
Maturity[5,7)	0.31	0.31	0.31	0

Duplicate table:- 

Sector	Gross	Net	Long	Short
Maturity[1,3)	55.07	43.8	49.44	0
Maturity[3,5)	34.04	21.63	27.84	0

[OTrieger]

@anonymous008_G 
You can create a header template that has all possible headers. Then union it with the data, so if you will not going to have that element it will come form the template, only ensure that you fix the data type of all element after the join as it will turn all of them to string type. I normally using a Text input that containing just the headers and union it to the rest of the data.

[anonymous008_G]

@OTrieger - nice idea, let me try this. thanks a lot

[anonymous008_G]

@OTrieger -- I did not get expected output workflow failed when short data was missing. Assuming below data in the unique block, how do i proceed further so that flow would work when Long or Short is missing. 

End goal is replace 0 values from unique table with duplicate table values for the respective column and row.

Can you please help me with your approach. i mean sample flow

Unique table :-

Sector	Gross	Net	Long	Short
Maturity(,1)	29.37	22.17	29.37	0
Maturity[1,3)	55.07	43.8	0	5.64
Maturity[10,)	0.25	0.25	0.25	0
Maturity[3,5)	34.04	21.63	0	6.2
Maturity[5,7)	0.31	0.31	0.31	0

Duplicate Table :-

Sector	Gross	Net	Long	Short
Maturity[1,3)	55.07	43.8	49.44	0
Maturity[3,5)	34.04	21.63	27.84	0

[OTrieger · Accepted solution]

[OTrieger]

I hope that this will help, you need to apply it at the beginning of your WF.

[anonymous008_G]

@OTrieger - thanks a lot, you have made my workflow much more easier with help of this idea. Earlier i was using union at the end of the flow, that's why it was not working. when i used at start of the flow and all worked as expected. Many thanks for your help.