Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I want to allow a comparison of a sales data table with a budget table,
And I can't join it to the sales table.
In a budget table I had to make a croostable (because there are no years and the month column is of type string and also includes the budget column).
I understand need to join with the fileds month and categoryid between sales table and budget table
but it's success.(All operations in section)
How can I correctly link the budget table to the sales table?
So that I can compare budget to sales?
Attached is a screenshot of the table structure and the relationships between the tables.
the table is structured like this:
Budget table:
CrossTable(month,Budget,1)
LOAD CategoryID,Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec FROM Budget.qvd
Sales table is structured as follows:
LOAD CustomerID,OrderDate,OrderID,ProductID,QTY,AMOUNT,CANCEL_FLAGFROM Sales.qvd
Product Table:
LOAD CategoryID,ProductID,ProductName,SupplierID,"TYPE" as [Prod Type] FROM Products.qvd
Category table:
LOAD CategoryID,"Category Desc",CategoryName FROM Categories.qvd
Thanks.
You could tackle this several ways. This is how I would do it:
// ******************************************
// ** Sales
// ******************************************
Sales:
LOAD
Num(OrderDate) as %Date,
ProductID as %ProductID,
CustomerID,
OrderDate,
OrderID,
QTY,
AMOUNT,
CANCEL_FLAG
FROM
Sales.qvd
// ******************************************
// ** Budget
// ******************************************
// Prep
Budget_tmp:
CrossTable(Month,Budget,1)
LOAD CategoryID,Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec FROM Budget.qvd
Set vBudgetYear = '2019'; // This should be whichever year your budget is over
Concatenate(Sales)
Load
'Budget-'&CategoryID as %ProductID,
Num(MakeDate($(vBudgetYear),Month(Date#(Month,'MMM')),1)) as %Date,
Budget
Resident
Budget_tmp;
// ******************************************
// ** Product
// ******************************************
Product:
LOAD
ProductID as %ProductID,
CategoryID as %CategoryID,
ProductID,
ProductName,
SupplierID,
"TYPE" as [Prod Type]
FROM
Products.qvd
// Filling in linking records for budget
Concatenate(Product)
Load
'Budget-'&CategoryID as %ProductID,
CategoryID as %CategoryID
Resident
Budget_tmp;
Drop Table Budget_tmp;
// ******************************************
// ** Category
// ******************************************
Category:
LOAD
CategoryID,
"Category Desc",
CategoryName
FROM Categories.qvd
// ******************************************
// ** Generate a calendar
// ******************************************
Calendar:
Load
%Date,
Date,
Month(Date) as Month,
Year(Date) as Year,
Dual('Q'&ceil(Month(%Date)/3)),ceil(Month(%Date)/3)) as Quarter
;
Load
FieldValue('%Date',RowNo()) as %Date // Load all date values
AutoGenerate
(FieldValueCount('%Date'));
This would allow you to make charts with Month, Quarter, Year, Category, Sum(Budget), and Sum(AMOUNT).
Thanks for the reply
But the connection still doesn't seem to work between the sales and budget tables
The values of sales separately
And the values of the budget separately
It's important to note that in the sales table the date column is for example 11/01/2019
and a date column in a budget table is like this: Jan
Screenshot attached