Concatenate with different dimensions

[#1]

Hi,

I have what is probably a simple question, but I have had QV for about two seconds and am stuck already so would really appreciate the help.

I have the below script (script 1) where I am trying to combine financial data - I am pulling in Actual sales via SQL, and then our budgeted sales via an Excel file and I am trying to concatenate the two tables to give me one table in QV which then allows me to pull data by scenarios (Actual and Budget).

I am then trying to pull out a data table which compares the actual sales against the budget sales by StatementCustomerShortName (see script 2) but the table does not connect the budgeted sales to the actual sales and puts all the budgeted sales against a zero StatementCustomerShortName.

Can you please let me know how I can get the budget data to sync correctly to all dimensions?

Many thanks,

Philipp

SCRIPT 1:

F8SALESANL:

Load *,left(CustomerAccountNumber,6) as StatementAccountNumber;

SQL SELECT

    IngCostTimeOfSale,

    InvoiceCreditDate,

    InvoiceCreditNoteIndicator,

    InvoiceCreditNoteNumber,

    MarginTimeOfSale,

    NominalPeriod,

    NominalPeriodPeriod,

    NominalPeriodYear,

    OverheadCostTimeOfSale,

    PackCostTimeOfSale,

    PartNumber,

    PriceTimeOfSales,

    SalesAnalysisItemID,

    SalesQuantity,

    TotalCostTimeOfSale,

    TotalValue,

    TransactionDate,

    ValueInKG,

    'Actual' as Scenario,

    (TotalValue-TotalCostTimeOfSale) as Contribution,

    CustomerAccountNumber

FROM F8ABI.dbo.F8SALESANL

where NominalPeriodYear >= 2013;

Concatenate

LOAD StatementAccountNumber,

     PartNumber,

     NominalPeriodYear,

     NominalPeriodPeriod,

     Scenario,

     SalesQuantity,

     TotalValue as TotalValue,

     TotalCostTimeOfSale,

     (TotalValue-TotalCostTimeOfSale) as Contribution    

FROM

(ooxml, embedded labels, table is [Budget14-15]);

SCRIPT 2:

SQL SELECT

    AccountType,

    AreaCode,

    BalanceCurrent,

    BalanceOutstandingTotal,

    BusinessSectorCode,

    Country,

    CreditLimit,

    CreditRatingCode,

    CustomerAccountNumber,

    CustomerBuyingGroupCode,

    CustomerCategoryCode,

    CustomerGroupDescription,

    CustomerName,

    CustomerShortName,

    DivisionCode,

    HomeExport,

    InterCompany,

    MaximsAreaCode,

    MaximsAreaDescription,

    PaymentTerms,

    Period1Arrears,

    Period2Arrears,

    Period3Arrears,

    Period4Arrears,

    Period5ArrearsPlus,

    RegionCode,

    SalesAreaCode,

    SalesRep,

    StatementAccountNumber,

    StatementCustomerShortName,

    CustomerStatus,

    VATCode,

    VATRegistationNumber

FROM F8ABI.dbo.F8CUSTOMER;