Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
danielle_v
Contributor

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

Re: Forecast vs. Shipped Volume - Joining 2 Tables

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

3 Replies
Not applicable

Re: Forecast vs. Shipped Volume - Joining 2 Tables

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

danielle_v
Contributor

Re: Forecast vs. Shipped Volume - Joining 2 Tables

That does indeed help... many thanks!

Not applicable

Re: Forecast vs. Shipped Volume - Joining 2 Tables

no worries, glad to help

Community Browser