Display data from 2 tables into single one

[#1]

Hi All,

Not sure that question subject is telling you sonething or not but I'll decribe my question:

I have imported 2 excel tables into QV so one is Actuals and the other is Budget. I want to create a variance table which will make difference between "Actual revenue" from Actuals and "Budgeted revenue" from Budget table. Revenue is split by Customer and Month. My proble is that I might have Budget for a customer which is not in actual in specific month so automatically that revenue will not be displayed.

Please help, if needed I can upload example database.

Thank you.

[hic]

Usually the best solution is to concatenate the two tables, and use

     'Actuals' as Type

and

     'Budget' as Type

in the two tables. The "Type" field can be used in Set Analysis.

HIC

[#3]

Thank you for reply.

I'm new to this so don't quite understand what is that Type.

I attached sample files so in Jan customer D is missing and in Feb customer A is missing.

Much appreaciate your help.

[rwunderlich · Accepted solution]

Looking at your data, I think you can just name both periods as "Period" and live with the synthetic key. If you can't live with the syn key, you can build a link table to replace it.

Or...just join the two tables together. They are the same granularity so that should work.

Actual:

LOAD Customer,

     Period,

     Revenue as Revenue_act

FROM

[test.xlsx]

(ooxml, embedded labels, table is Actuals);

JOIN (Actual)

LOAD Customer,

     Period,

     Revenue as Revenue_Bud

FROM

[test.xlsx]

(ooxml, embedded labels, table is Budget);

-Rob

[#5]

HI Rob,

I applied your answer to our business model and it worked!

Thank you for this simple and great solution.

Regards,

Eugen