topic Linking 2 tables where not all data matches in QlikView

Linking 2 tables where not all data matches

danielle_v — Fri, 10 Oct 2014 16:09:30 GMT

Hi All,

I'm trying to do some analysis whereby I need to compare forecasted vs. actual volumes.

The actual volumes are already in Qlikview, and the forecasted volumes are read in from an Excel spreadsheet.

My issue is with handling records which do not match. As per the below table, the actual and forecasted volumes are linked by a key made up of Country and Supplier. Where there is a country and supplier with both actual and forecasted volume, everything works fine and correctly returns data. My issue is where there is a country/supplier with actual but not forecasted volume, or forecasted but not actual volume - like the yellow highlighted rows in the below example. In scenarios such as this, the record will not exist in the table of data - for example, Australia4 will not exist in my Excel forecast data.

Country	Supplier	KEY	Actual Volume	Forecasted Volume
China	1	China1	50	40
India	2	India2	20	50
UK	3	UK3	30	30
Australia	4	Australia4	100	0
USA	5	USA5	0	20

Whatever I do, I can't get ALL of the data to show in my table, there's always something missing.

I'm hoping there's a straight-forward solution to this that I've missed - any help much appreciated!

Re: Linking 2 tables where not all data matches

anbu1984 — Fri, 10 Oct 2014 16:42:43 GMT

In case of missing data, what do you want to do? Do you want to ignore such data in your chart?

Re: Linking 2 tables where not all data matches

maxgro — Fri, 10 Oct 2014 17:12:54 GMT

if you want to show the KEY with forecast and actual you can try with (same for actual)

if(sum([Forecasted Volume])>0 and sum([Actual Volume])>0, sum([Forecasted Volume]))

in total mode use sum of rows

Re: Linking 2 tables where not all data matches

danielle_v — Tue, 25 Nov 2014 16:15:37 GMT

Thanks for the responses on here, and so sorry for the delay in getting back to you.

I need to be able to include ALL records, regardless of whether they appear in the Actual Volume, the Forecasted Volume or both - so as per the example above, I need all 5 rows (including the yellow rows) to appear in my analysis.

I've tried left joining, but depending on which way I join, either the records where the Forecasted Volume is zero or the records where the Actual Volume is zero, are excluded.

I need to be able to get to a point where I can show the full picture of forecast and actuals, without any reocrds missing.

Any help still greatly appreciated!

Re: Linking 2 tables where not all data matches

jpapador — Tue, 25 Nov 2014 16:24:32 GMT

Try loading up the table with the actuals and then do an OUTER JOIN to bring in the forecast. This way all records will be brought in.

You can then set Null values in the script to zero:

If(Len(Actual) = 0, 0, Actuals) as Actuals,

If(Len(Forecast) = 0, 0, Forecast) as Forecast

Re: Linking 2 tables where not all data matches

Tue, 25 Nov 2014 17:09:40 GMT

Another option you have is to concatenate the actual and forecast data and identify each with a data source field in the script.

You then have slightly modified expressions for actual and forecast using set analysis to limit to the relevant data source.

Fact:

Load

Country

Supplier

Country & Supplier AS KEY

Volume

'Actual' AS DATA_SOURCE

from your actual source here

Concatenate(Fact)

Load

Country

Supplier

Country & Supplier AS KEY

Volume

'Forecast' AS DATA_SOURCE

from your forecast source here

Then your expressions become

Sum({<DATA_SOURCE={'Actual'}>} Volume)

Sum({<DATA_SOURCE={'Forecast'}>} Volume)

hope that helps

Joe