9 Replies Latest reply: Sep 15, 2014 6:15 PM by Jayanthan Sivaloganathan RSS

    Join tables

      Hi All

       

      I have tried to join different table and appreciate if anyone confirm whether i have done correctly

       

      Directory Q:\DataSource\Excel\Sales\SalesOrderBook;
      For each ExcelFile in filelist ('*.xlsm')
      CORE:

      Load 

      [MERCHANDISING MANAGER]AS MANAGER,
      BUYER,
      [SEASON CODE],
      [PHASE/BUY],
      [TOP CATERGORIES],
      GARMENTS,
      [PACK CODE/DESCRIPTION],
      PROGRAM,
      RANGE,
      [PRODUCT DESCRIPTION],
      [ITEM CODE],
      [PRODUCT CODE],
      [COUNTRY CODE],
      COUNTRY,
      [MODE-ORIGINAL],
      [MODE- REVISED],
      PO,
      ARTICLE,
      [BUYER STYLE ID],
      [PACK QTY],
      [NO OF PACKS],
      [ORDER QTY],
      [ORDER QTY]/[NO OF PACKS] as PLANNEDPACKS,
      [EX FACTORY ORIGINAL],
      [EX FACTORY REVISED],
      [IHOD/ACTUAL EX FACTORY],
      [FOB PO DATE],
      [REVISED FOB],
      [CRD/VESSEL DATE],
      [EX COUNTRY DATE],
      [REVISED EX COUNTRY DATE],
      NDC,
      [SALES DATE]as DATE,
      [SALES MONTH],
      [FOB-PACK],
      COMMISSION,
      [FOB WITHOUT COMMISSION],
      [TOTAL VALUE AS PER  ORDER],
      [SHIPPED QTY],
      [SHIPPED QTY]/[NO OF PACKS] as SHIPPEDPACKS,
      [SHIPMENT VALUE],
      [INVOICE NO],
      [QTY VARIANCE],
      [SHIPPED STATUS],
      [PLANNED SMV],
      [PLANNED CM],
      [PLANNED TOTAL VALUE],
      [PLANNED CM/MINUTE],
      [PLANNED SAH],
      [SALES LOCATION],
      [CM PORTION FOR SALES LOCATION],
      [QTY FOR PRODUCTION LOCATION],
      [KGL-CM],
      [TLD-CM],
      [TAL-CM],
      [PAN-CM],
      [PRODUCTION QTY KGL],
      [PRODUCTION QTY TLD],
      [PRODUCTION QTY TAL],
      [PRODUCTION QTY PAN],
      [SHIPPED QTY KGL],
      [SHIPPED QTY TLD],
      [SHIPPED QTY TAL],
      [SHIPPED QTY PAN]


      From $(ExcelFile) (ooxml, embedded labels, table is [CORE]);
      Next ExcelFile;

      join

      LOAD * INLINE [
      COMPANY, SALES LOCATION
        EMJAY, KGL
      EMJAY, TLD
      PENGUIN, TAL
      PENGUIN, PAN
      ]
      ;

      join
      LOAD * INLINE [
      COMPANY,BUYER,MANAGER
      EMJAY,TESCO OW,HARSHANA
      EMJAY,TESCO UW,HARSHANA
      EMJAY,H&M,NELUN
      EMJAY,LEVIS,NELUN
      EMJAY,HUGO BOSS,SUCHITRA/DARREL
      EMJAY,DIESEL,SUCHITRA/SHAZRI
      EMJAY,LA PERLA,SUCHITRA/SHAZRI
      PENGUIN,HELLY HANSEN,NADEEKA
      PENGUIN,MERRELL,PIYUMI
      PENGUIN,GAP/OLD NAVY,WIDURANGA
      PENGUIN,GAP,WIDURANGA
      PENGUIN,OLD NAVY,WIDURANGA

      ]
      ;

      join

      LOAD BUYER,
      DATE,
      BAMOUNT,
      COMPANY,
      MANAGER
      FROM
      Q:\DataSource\Excel\Sales\BUDGET.xlsx
      (
      ooxml, embedded labels, table is Sheet1);

      join
      LOAD COMPANY,
      DATE,
      BUYER,
      [SHIPMENT VALUE],
      BAMOUNT,
      MANAGER
      FROM
      Q:\DataSource\Excel\Sales\SALES20122014.xlsx
      (
      ooxml, embedded labels, table is Sheet1);