Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
henster36
New Contributor III

How to input a field and joining the tables

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.

Tags (1)
1 Solution

Accepted Solutions

Re: How to input a field and joining the tables

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

3 Replies

Re: How to input a field and joining the tables

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

henster36
New Contributor III

Re: How to input a field and joining the tables

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?

Re: How to input a field and joining the tables

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

Community Browser