Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
shay1005
Contributor
Contributor

Difficulty join a specific table( in data load editor

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.

 

 

Labels (3)
2 Replies
treysmithdev
Partner Ambassador
Partner Ambassador

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).

Blog: WhereClause   Twitter: @treysmithdev
shay1005
Contributor
Contributor
Author

 

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