Qlik Community

Ask a Question

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Welcome to our newly redesigned Qlik Community! Read our blog to learn about all the new updates: READ BLOG and REPORTED ISSUES
cancel
Showing results for 
Search instead for 
Did you mean: 
Creator
Creator

Forecast vs. Shipped Volume - Joining 2 Tables

Hi All,

I need to do some analysis around forecasted vs. shipped volumes. The shipped volumes are basically the sum of units shipped by month and country.

The forecasted volumes are read in from an external Excel spreadsheet, and are also set out in a similar way, showing the forecasted units by month and country.

I am trying to join these 2 tables together using a key based on month and country, so that I can output the figures as follows, showing the forecasted vs. shipped volumes by country and month;

CountryMonthForecastShipped
IndiaMay 141000700
ChinaMay 1420001500
TurkeyMay 14800900

The problem comes where there is a country/month combination in one of the tables but not the other - for example if there was volumes forecasted to ship from China in Jun 14 but nothing shipped, or vice versa. Basically, I need to show all combinations of month and country, whether they appear in one or both of the tables. I believe if I left join one table to the other, I will only get values for the country/months that appear in the first table?

Is this possible?

Any help greatly appreciated!

1 Solution

Accepted Solutions
Not applicable

Hi Danielle,

you want to concatenate your two tables together rather than join. Load each one with a measure type field (for Shipped and Forecast). Then you can use that measure type field within each of your expressions to give your value split (or pivot and drag measure type up within your table)

Edit:

Fact:

load

Country,

Month,

Amount,

'Forecast' as Measure_type

From xxxx

Concatenate (Fact)

load

Country,

Month,

Amount,

'Shipped' as Measure_type

From xxxx

hope that helps

Joe

View solution in original post

3 Replies
Not applicable

Hi Danielle,

you want to concatenate your two tables together rather than join. Load each one with a measure type field (for Shipped and Forecast). Then you can use that measure type field within each of your expressions to give your value split (or pivot and drag measure type up within your table)

Edit:

Fact:

load

Country,

Month,

Amount,

'Forecast' as Measure_type

From xxxx

Concatenate (Fact)

load

Country,

Month,

Amount,

'Shipped' as Measure_type

From xxxx

hope that helps

Joe

View solution in original post

Creator
Creator

That does indeed help... many thanks!

Not applicable

no worries, glad to help