topic Re: Display data from 2 tables into single one in QlikView

Display data from 2 tables into single one

Mon, 24 Feb 2014 18:58:52 GMT

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.

Re: Display data from 2 tables into single one

hic — Mon, 24 Feb 2014 19:02:08 GMT

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

Re: Display data from 2 tables into single one

Mon, 24 Feb 2014 19:19:52 GMT

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.

Re: Display data from 2 tables into single one

rwunderlich — Mon, 24 Feb 2014 19:40:30 GMT

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

Re: Display data from 2 tables into single one

Tue, 25 Feb 2014 13:36:59 GMT

HI Rob,

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

Thank you for this simple and great solution.

Regards,

Eugen