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

[#2]

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

[alexandros17]

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

[#4]

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

[jonathandienst]

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

[#6 · Accepted solution]

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