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
Vendor	A	Planned	100	Quarter	Q1	Segment	X
Vendor	A	Planned	150	Quarter	Q1	Segment	Y
Vendor	A	Planned	100	Quarter	Q1	Segment	Z
Vendor	B	Planned	100	Quarter	Q1	Segment	X
Vendor	B	Planned	175	Quarter	Q1	Segment	Y
Vendor	C	Planned	175	Quarter	Q1	Segment	X
Vendor	C	Planned	250	Quarter	Q1	Segment	Y
Vendor	C	Planned	150	Quarter	Q1	Segment	Z
Vendor	D	Planned	125	Quarter	Q1	Segment	Z
Vendor	A	Planned	100	Quarter	Q2	Segment	Y
Vendor	A	Planned	150	Quarter	Q2	Segment	Z
Vendor	B	Planned	100	Quarter	Q2	Segment	X
Vendor	B	Planned	100	Quarter	Q2	Segment	Y
Vendor	B	Planned	175	Quarter	Q2	Segment	Z
Vendor	C	Planned	175	Quarter	Q2	Segment	X
Vendor	C	Planned	250	Quarter	Q2	Segment	Z
Vendor	D	Planned	150	Quarter	Q2	Segment	Y
Vendor	D	Planned	125	Quarter	Q2	Segment	Z

Actual Table:

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

Final Table:

Vendor	Q1		Q2
Vendor		Q1	Planned	Q2	Total Paid		Planned		Total Paid
Vendor	A	Q1	350	Q2	360		250		365
Vendor	B	Q1	275	Q2			375
Vendor	C	Q1	575	Q2			425
Vendor	D	Q1	125	Q2	155		275		155
Vendor	E	Q1		Q2					95
Vendor	Grand Total	Q1	1325	Q2	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