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;
