0 Replies Latest reply: Apr 20, 2012 4:20 PM by Scott Simmons RSS

    Outer Join to a Spreadsheet

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