topic Re: Using The JOIN Function in QlikView

Using The JOIN Function

Thu, 23 Oct 2014 09:35:29 GMT

Hi,

I am trying to add a field from table 1 into table 2 so I can link tabel 2 to table 3. I need to join table 2 to table 3 on a certain field that is not already in table 2. Everytime I use JOIN to do this, my PC breaks down and I have to re-start the computer. Can anyone see an issue that would cause my computer to meltdown;

I am putting this straight after the table that I want to add the field into;

Left Join (sales)

LOAD

financial_id as financial_id,

id as cust_id,

cust&' - '&name as [Account Name]

From [..\Data\customer.QVD] (qvd)

The field I need to add is - financial_id

Re: Using The JOIN Function

alexandros17 — Thu, 23 Oct 2014 09:36:49 GMT

post all the load sequence please

Re: Using The JOIN Function

Thu, 23 Oct 2014 09:44:30 GMT

ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='£#,##0.00;-£#,##0.00';

SET TimeFormat='hh:mm:ss';

SET DateFormat='DD/MM/YYYY';

SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

Directory;

LOAD id as ceesaid,

     parent,

     shortdesc as ceesa_group,

     shortdesc&' - '&description as [CEESA],

     description as ceesadescription,

     fulldesc

FROM

[..\Data\ceesagroup.QVD]

(qvd);

LOAD cust,

     del,

     id as custid,

     financial_id,

     cust&' - '&name as [Account],

     name,

     street1,

     street2,

     town,

     county,

     postcode,

     salesrepid,

     sales_rep,

     custservicerepid,

     cust_service_rep,

     van_run_id,

     van_run,

     groupid,

     cust_status,

     prm_cust_type,

     cust_type,

     payment_method

FROM

[..\Data\customer.QVD]

(qvd);

LOAD emp_id,

     emp_name

FROM

[..\Data\employee.QVD]

(qvd);

LOAD FiscalDate as inv_date,

     Year,

     Period,

     [Period Day],

     [Period Week],

     [Period Week Year],

     Month,

     Quarter#,

     Quarter,

     WeekDay,

     WeekYear,

     CurYTDFlag,

     LastYTDFlag,

     FirstDayOfPeriod01,

     LastDayOfPeriod01,

     FirstDayOfPeriod,

     LastDayOfPeriod,

     YearPeriod,

     YearQuarter,

     CurrentPeriodActual,

     CurrentQuarterActual,

     CurrentPeriod,

     CurrentQuarter,

     CalendarMonth,

     CalendarMonthNum,

     CalendarQuarter,

     CalendarYear,

     CalendarYearQuarter,

     CalendarWeek,

     CalendarDay,

     FiscalYTDFlag,

     CalendarYTDFlag,

     WorkDayType,

     IsAWorkDay,

     FirstWorkingDay,

     LastWorkingDay,

     '01/'&CalendarMonthNum&'/'&CalendarYear as period

FROM

[..\Data\FiscalCalendar.QVD]

(qvd);

LOAD pcode,

pcode&' - '&description as [Product],

     description,

     legal_cat,

     cost_price,

     list_price,

     ceesa_group,

     top_ceesa_category,

     supp_no,

     supp_name,

     prod_status,

     private_brand,

     supp_pcode,

     stocking_type,

     pseudo_item,

     hidden_item,

     product_cat,

     remote_orderable,

     remote_enabled,

     discontinued

FROM

[..\Data\product.QVD]

(qvd);

LOAD lineno,

     orderno,

     invoice_id,

     inv_date,

     line_pcode as pcode,

     line_desc,

     ord_qty,

     inv_qty,

     line_value,

     vat_value,

     ord_account_id as custid

FROM

[..\Data\sales.QVD]

(qvd);

Join(sales)

LOAD

     financial_id as financial_id,

     id as cust_id,

     cust&' - '&name as [Account Name]

From [..\Data\customer.QVD] (qvd) ;

Re: Using The JOIN Function

maternmi — Thu, 23 Oct 2014 09:51:00 GMT

Hi,

if I see right you try to load "id as cust id" but in the customer qvd there is no "id" only "cust id".

Michael

Re: Using The JOIN Function

alexandros17 — Thu, 23 Oct 2014 09:51:13 GMT

The problem is that for each row in sales you join all the rows in customer because there are not commom fields, in this way you get an enormous table that crash your system,

the thing you can do is calling in the same way (the same field name) for cust_id so write cust_id in bith sales and customer tables

let me know

Re: Using The JOIN Function

maternmi — Thu, 23 Oct 2014 09:53:35 GMT

furthermore if you would like to join to a specific table then you need also the id of this table in your join statement for sales table is it invoice id or cust id

Re: Using The JOIN Function

jonathandienst — Thu, 23 Oct 2014 10:01:08 GMT

There are no common fields between Sales and the Join LOAD statement, so QV is performing a cartesian (or cross) join.Every row of sales is being joined in turn to every row from customer.qvd.

This is almost certainly not what you want and equally certainly is running out of memory on your PC.

You have custid in Sales and cust_id in your load - these should probably be the same. I dont think Account Name is needed here - let QV take care of that via association.

Left Join(sales)

LOAD Distinct

id as custid,

financial_id as financial_id,

From [..\Data\customer.QVD] (qvd) ;

(although you would be better off doing this through a mapping rather than a join).

HTH

Jonathan

Re: Using The JOIN Function

Thu, 23 Oct 2014 10:01:59 GMT

I do have cust_id in both customer and sales tables by using the as function. Would this not act the same?

For the second query, I thought by putting (sales) next to the join statement

Re: Using The JOIN Function

alexandros17 — Thu, 23 Oct 2014 10:04:46 GMT

The field name must be equal so custid is different from cust_id, write them in the same way and your join will link the customer information to the sales information with your join

Re: Using The JOIN Function

jonathandienst — Thu, 23 Oct 2014 10:08:00 GMT

But do you need the join at all?

QV will associate the records in Sales with the records in Customer based on custid, so you will be able to report sales on financial_id without performing the join.

Jonathan

Re: Using The JOIN Function

Thu, 23 Oct 2014 10:14:18 GMT

The problem I am trying to solve is, I need to show sales based on the financial id (so this groups multiple accounts) but I need to pull the information from the sales table as I also need invoice data (sales is the only table to hold the invoice data). So what I am trying to do is add financial id into the sales table. There may well be a better way to do this but i'm a new user and I am trying to build a reporting tool from scratch. Any ideas/suggestions on how I could do this in a better way would be appreciated

Re: Using The JOIN Function

alexandros17 — Thu, 23 Oct 2014 10:22:04 GMT

The suggestion given from me and Jonathan dienst are correct, You can do it with join (I should use Left Join) but you can do (like suggested from Jonathan) even without join, data will associate by common fields

Re: Using The JOIN Function

Thu, 23 Oct 2014 10:35:17 GMT

I have tried doing this on custid but it only brings results back based on that 1 account. I need to show other accounts that are linked together by financial id. i.e I have 3 accounts with different names/custid's but they are owned by the same person and they want to see what all 3 have bought in total as well as by account. I need to show sales based on financial id as the default and also have custid in to show the split in account. Hope this makes sense

Re: Using The JOIN Function

jonathandienst — Thu, 23 Oct 2014 10:48:46 GMT

If you select a custid, you will get only that customer's sales; if you select a financial_id, you will get teh sales for all custids in that financial_id --- without needing a join. That's how QV works.

Use a pivot table with financial_id and custid (or name) as dimensions.

Jonathan

Re: Using The JOIN Function

Thu, 23 Oct 2014 10:51:05 GMT

It is getting that join in the cutomer table that I couldn't get. I've fixed my script and it seems to be working. I've now got;

ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='£#,##0.00;-£#,##0.00';

SET TimeFormat='hh:mm:ss';

SET DateFormat='DD/MM/YYYY';

SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

Directory;

LOAD id as ceesaid,

     parent,

     shortdesc as ceesa_group,

     shortdesc&' - '&description as [CEESA],

     description as ceesadescription,

     fulldesc

FROM

[..\Data\ceesagroup.QVD]

(qvd);

LOAD cust,

     del,

     id as custid,

     financial_id,

     cust&' - '&name as [Account],

     name,

     street1,

     street2,

     town,

     county,

     postcode,

     salesrepid,

     sales_rep,

     custservicerepid,

     cust_service_rep,

     van_run_id,

     van_run,

     groupid,

     cust_status,

     prm_cust_type,

     cust_type,

     payment_method

FROM

[..\Data\customer.QVD]

(qvd);

LOAD emp_id,

     emp_name

FROM

[..\Data\employee.QVD]

(qvd);

LOAD FiscalDate as inv_date,

     Year,

     Period,

     [Period Day],

     [Period Week],

     [Period Week Year],

     Month,

     Quarter#,

     Quarter,

     WeekDay,

     WeekYear,

     CurYTDFlag,

     LastYTDFlag,

     FirstDayOfPeriod01,

     LastDayOfPeriod01,

     FirstDayOfPeriod,

     LastDayOfPeriod,

     YearPeriod,

     YearQuarter,

     CurrentPeriodActual,

     CurrentQuarterActual,

     CurrentPeriod,

     CurrentQuarter,

     CalendarMonth,

     CalendarMonthNum,

     CalendarQuarter,

     CalendarYear,

     CalendarYearQuarter,

     CalendarWeek,

     CalendarDay,

     FiscalYTDFlag,

     CalendarYTDFlag,

     WorkDayType,

     IsAWorkDay,

     FirstWorkingDay,

     LastWorkingDay,

     '01/'&CalendarMonthNum&'/'&CalendarYear as period

FROM

[..\Data\FiscalCalendar.QVD]

(qvd);

LOAD pcode,

pcode&' - '&description as [Product],

     description,

     legal_cat,

     cost_price,

     list_price,

     ceesa_group,

     top_ceesa_category,

     supp_no,

     supp_name,

     prod_status,

     private_brand,

     supp_pcode,

     stocking_type,

     pseudo_item,

     hidden_item,

     product_cat,

     remote_orderable,

     remote_enabled,

     discontinued

FROM

[..\Data\product.QVD]

(qvd);

Sales:

LOAD lineno,

     orderno,

     invoice_id,

     inv_date,

     line_pcode as pcode,

     line_desc,

     ord_qty,

     inv_qty,

     line_value,

     vat_value,

     ord_account_id as cust_id

FROM

[..\Data\sales.QVD]

(qvd);

left Join

LOAD

     financial_id as financial_id,

     id as cust_id,

     cust&' - '&name as [Account Name]

From [..\Data\customer.QVD] (qvd) ;