How to input a field and joining the tables

[Anonymous]

Hi, I'm not too familiar using the JOIN statement or how to join tables so I hope one of you clever guys out there can help me.

I've got two tables for sales for two different branches for which I want to load data. The field names in the Sales tables are exactly the same so they are automatically joined. There are no fields within the tables which indicates for which branch the data is. I therefore want to associate the tables with a specific branch by loading a field name "Branch", with the different branches as values within the field. I loaded a table using inline after each Sales table to input the branches and used the JOIN statement to join the inline table with the sales table (see below).

//SalesMasterJHB
Directory;
SalesMaster:
LOAD
           DocumentNumber, 
     DocumentType, 
     Date, 
     Period, 
     CustomerCode, 
     CustomerName, 
     [Customer Code Name], 
     SalesmanCode, 
     SalesmanName, 
     OrderNumber, 
     CostCode, 
     DeliveryAddress01, 
     DeliveryAddress02, 
     DeliveryAddress03, 
     DeliveryAddress04, 
     DeliveryAddress05, 
     Description, 
     QuoteClosingDate, 
     ProductCode, 
     [Product Name], 
     [Product Code Name], 
     [Product Category], 
     Unit, 
     Quantity, 
     UnitCost, 
     TotalLineCost, 
     UnitSell, 
     InclusivePrice, 
     TotalBeforeDiscount, 
     TotalDiscountPercent, 
     TotalDiscount, 
     TotalLineSale, 
     TaxAmount, 
     [Line Total Incl], 
     GP, 
     Physical, 
     StoreCode
FROM
[...\Sales Master 1 August - 31 August 2012 final JHB.xlsx]
(ooxml, embedded labels, table is Sheet1);
JOIN LOAD * Inline [
           Branch,
           JHB];
//SalesMasterBloem
Directory;
LOAD DocumentNumber, 
     DocumentType, 
     Date, 
     Period, 
     CustomerCode, 
     CustomerName, 
     [Customer Code Name], 
     SalesmanCode, 
     SalesmanName, 
     OrderNumber, 
     CostCode, 
     DeliveryAddress01, 
     DeliveryAddress02, 
     DeliveryAddress03, 
     DeliveryAddress04, 
     DeliveryAddress05, 
     Description, 
     QuoteClosingDate, 
     ProductCode, 
     [Product Name], 
     [Product Code Name], 
     [Product Category], 
     Unit, 
     Quantity, 
     UnitCost, 
     TotalLineCost, 
     UnitSell, 
     InclusivePrice, 
     TotalBeforeDiscount, 
     TotalDiscountPercent, 
     TotalDiscount, 
     TotalLineSale, 
     TaxAmount, 
     [Line Total Incl], 
     GP, 
     Physical, 
     StoreCode
FROM
[...\Sales Master 1 AUG - 31 AUG 2012 - BLOEM (FINAL).xlsx]
(ooxml, embedded labels, table is Sheet1);
JOIN LOAD * Inline [
           Branch,
           Bloem];

However, when I reload the data, the script execution Progress stops at the following stage and the program doesn't respond once I abort the reloading process:

SalesMaster << Sheet1 1,607 lines fetched
INLFED << INLCC14 1 lines fetched
Sheet1 1,754 lines fetched
INLFED << INLCCC1 1 lines fetched

Where can I change the script to correct it or is there an easier way to join fields with other tables? Any help will be much appreciated.

[Miguel_Angel_Baeyens · Accepted solution]

Hi Hennie,

JOINs in QlikView happen when at least one of the fields in both tables are named alike. It seems that the Brach field only exists in your INLINE tables but not in your source LOADs, so it's likely to be killing QlikView making it all possible combinations between any of the rows in each table and Branch...

EDIT: Ok, it seems I understood wrong your issue. I'd do the following:

SalesMaster:
LOAD *,
     'JHB' AS Branch
FROM JHBFile ...;
CONCATENATE (Table1) LOAD *,
     'Bloem' AS Branch
FROM BloemFile ...;

Hope that makes sense.

Miguel

[Anonymous]

Hi Miguel. Thanks for your reply. I changed the script and it works. Didn't think that to input new fields was as easy as "'JHB' as Branch". Just a question, is it necessary to use the "CONCATENATE" statement? And whats the function for inputting the Table name in brackets? It is to tell Qlikview which table (if you have more than one table in the script) to Concatenate with the table being loaded?

[Miguel_Angel_Baeyens]

Hi Hennie,

You're welcome! First: QlikView concantenates automatically when in the model are two tables or more that have the same exact number and name of fields, case sensitive. Second, it's best practice, although not mandatory, to make the code clearer, to specify that you are going to CONCATENATE (append) the lines in the coming table to the previously loaded table.

As the script is executed from top to down sequentially, you may load a table in a different tab, and it makes the code easier to read when you specify the table you want to concatenate to.

Hope that helps.

Miguel