Outer Join to a Spreadsheet

Fri, 20 Apr 2012 20:20:27 GMT

I have been given a spread sheet that contains fields(plant_name, close_period,prod_rpt_classification) from three different tables and a budget number that is associated it. I have gone back and forth trying different things to get it to load and either I get a circular reference or it restricts my dash board to just the data that exists in the file. I'm a newbie so I'm sure it's something simple that I do not know. I have listed the three tables below with the spreadsheet being the last line in the script. Any help you could give would greatly be appreciated. By the way, I have validated that the data in file matches up to the data in the three fields.

SQL SELECT
"record_id" as plant_record,
"plant_code",
"plant_name",
address1 as plant_address,
city as plant_city,
state as plant_state,

    "bl_printed" as plant_blprinted,
    country as plant_country,
    "cut_off_days" as plant_cutoffdays,
    "cut_off_time" as plant_custofftime,
    "display_name" as plant_display_name,
    faxnumber as plant_fax,
    inactive as plant_inactive ,
    "max_trucks_per_day" as plant_trkperday,
    "phone_day" as plant_phone_day,
    "phone_evening" as plant_phone_evening,
    "production_plant" as plant_prodflg,
    "vmi_flag" as plant_vmiflg,
    "warehouse_num" as plant_whse,
    zip
FROM "DBA"."order_plant";

SQL SELECT
    cast("plant_record" as varchar(10)) +
   '/' + cast("close_period" as varchar(10)) +
   '/' + "product_code" as BalanceKey,
   year("close_period") as trans_year,
month("close_period") as trans_month,
quarter("close_period") as trans_quarter,
"plant_record",
"close_period",
    "beg_balance",
    "product_code" as product,
    "quantity" as end_balance
FROM "DBA"."fg_inventory_balances"

SQL SELECT "approval_required",

"bag_id",

bagpieces,

"bags_per_bundle",

"barcode_id",

"changed_by",

/* comments, */

density,

"desc" as prod_desc,

facing as facing_code,

/* "gp_item_number", */

inactive as prod_inactive,

incompletemasters,

label as label_code,

"last_updated" as prod_last_update,

length as prod_length,

"length_uom" as prod_lengthuom,

loadfact ,

masterbags,

pkgtype as package_id,

pricetyp,

"prod_rpt_classification",

product,

"production_rvalue",

rvalue,

sku,

sqftpkg,

thickness,

"thickness_uom",

upc,

weight,

width as prod_width,

"width_uom" as prod_widthuom

FROM "DBA".productlist;

budget:
LOAD budget_plant as plant_name,
     budget_month as close_period,
     budget_category as prod_rpt_classification,
     budget_lbs
FROM
E:\01.Repository\EXTERNAL\PlantBudget\PlantBudget2012.xls
(biff, embedded labels, table is ProductionData$);

topic Outer Join to a Spreadsheet in QlikView

Outer Join to a Spreadsheet