How to build a one table to show data from two different datasets in workbook.

Rajakumar Burra

I have one sheet which is created manually as planned expenses and actual data is fetching from Oracle fusion.

I want a table for wach quarter or month to compare actual vs planned.

Example as below.

Test Two tables.xlsx

Planned table.

Vendor	Planned	Quarter	Segment
A	100	Q1	X
A	150	Q1	Y
A	100	Q1	Z
B	100	Q1	X
B	175	Q1	Y
C	175	Q1	X
C	250	Q1	Y
C	150	Q1	Z
D	125	Q1	Z
A	100	Q2	Y
A	150	Q2	Z
B	100	Q2	X
B	100	Q2	Y
B	175	Q2	Z
C	175	Q2	X
C	250	Q2	Z
D	150	Q2	Y
D	125	Q2	Z

Actual Table:

	ID		Sub ID		Vendor		Paid		Total Paid		Quarter		Segment
	1		1.1		A		75		95		Q1		X
	1		1.2		A		20		95		Q1		X
	2		2.1		A		155		155		Q1		Y
	3		3.1		A		50		110		Q1		Z
	3		3.2		A		20		110		Q1		Z
	3		3.3		A		40		110		Q1		Z
	4		4.1		D		175		155		Q1		Z
	4		4.2		D		-20		155		Q1		Z
	5		5.1		A		100		100		Q2		X
	9		9.1		E		120		95		Q2		Z
	6		6.1		A		155		155		Q2		Y
	7		7.1		A		50		110		Q2		Z
	7		7.2		A		20		110		Q2		Z
	7		7.3		A		40		110		Q2		Z
	8		8.1		D		155		155		Q2		Z
	8		8.2		D		0		155		Q2		Z

Final Table:

Vendor	Q1		Q2
	Planned	Total Paid	Planned	Total Paid
A	350	360	250	365
B	275		375
C	575		425
D	125	155	275	155
E				95
Grand Total	1325	515	1325	615

Chere-Oracle

Hi @Rajakumar Burra ,

I recommend you create calculations for Vendor and Quarter using the IFNULL() function, like this:

Then you can use this calculated Vendor and Quarter in your pivot table.

Chere-Oracle

You can join the two datasets by Vendor and Quarter. Or you can create a dataset that includes both tables—the manual table and the one fetched from Oracle Fusion — joined by Vendor and Quarter. This pre-joined dataset would, in a workbook, display two tables.

Rajakumar Burra

@Chere-Oracle Please can you create a DV with attached spread sheet data and final results like shown in Final sheet in spread sheet.

Highly appreciated your help. I want this without Dataflow. I am fine with datasets join in Workbook or join two in Dataset.

Chere-Oracle

Hi Rajakmar,

I tried it, and there is a problem. The join between the two tables — when joined on common columns Vendor, Quarter, and Segment — gives multiplied Planned values due to the Actuals break-down by Sub ID. It would be best to join Planned with an aggregated Actuals table, grouped by Vendor, Quarter, and Segment (so that it has only 1 row per group). Otherwise, it would take some finagling to get rid of the multiplication.

If you cannot get the aggregated Actuals table for some reason, you could add a filter in the dataset's table definition, to filter the rows to only the Sub IDs that have .1 (I mean, a 1 in the first decimal position), and then use the Total Paid column in your pivot, instead of the Paid column.

PS: I noticed there is an error in the Total Paid totals. They don't all match the sub totals of the Paid column.

Rajakumar Burra

Thanks @Chere-Oracle Still I am unable to get what I want without Dataflow.

I can get the totals accurate using aggregates but missing Vendors (B,C) and adding empty quarter.

Attached the Workbook. Please let me know if I am missing something.

Test Tables.zip