Using The JOIN Function

[#1]

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

[alexandros17]

post all the load sequence please

[#3]

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

[maternmi]

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

BR

Michael

[alexandros17]

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

[maternmi]

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

[jonathandienst]

Hi

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

[#8]

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

[alexandros17]

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

[jonathandienst]

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