4 Replies Latest reply: Feb 11, 2014 9:20 PM by cyril tra RSS

    How to use Join tables then concatenate them into 1 table

      Hello,

       

      my objective is too have 1 and only 1 large fact table in my qvd.

       

      Right now, I have 5 tables: 4 facts tables (sales, stock, orders, delivery) with 1 key (different from each table) and 1 product master.

       

      My idea is to:

      1) create first the 4 facts tables with product info ==> Using JOIN in load

      2) Then concatenate the 4 tables into only 1 "data" table

       

      I succeed to do 1), but how can I do 2) ?

       

      Here is my load statement that achieve 1):

       

      STOCK:

      LOAD key1,

           qty

      FROM stock.xlsx

      (ooxml, embedded labels, table is Sheet1)

      WHERE qty <> 0;

       

      LEFT JOIN (STOCK)

      LOAD key1,

          key2,

          key3,

          key4,

          dimA

          dimB,

          dimC,

          dimD,

          dimE,

          dimF

      FROM master.xls

      (biff, embedded labels, table is Sheet1);

       

       

      SALES:

      LOAD key2,

           [Sold Qty]

      FROM sales.xlsx

      (ooxml, embedded labels, table is Sheet1)

      WHERE [Sold Qty] <> 0;

       

      LEFT JOIN (SALES)

      LOAD key1,

          key2,

          key3,

          key4,

          dimA

          dimB,

          dimC,

          dimD,

          dimE,

          dimF

      FROM master.xls

      (biff, embedded labels, table is Sheet1);

       

       

      DELIVERY:

      LOAD key3,

           [Estimated Arrival Date],

           [Delivered Qty] as "In Transit Qty"

      FROM delivery.xls

      (biff, embedded labels, table is Sheet1);

       

      LEFT JOIN (DELIVERY)

      LOAD key1,

          key2,

          key3,

          key4,

          dimA

          dimB,

          dimC,

          dimD,

          dimE,

          dimF

      FROM master.xls

      (biff, embedded labels, table is Sheet1);

       

       

      ORDER:

      LOAD key4,

           [Launch Date]

      FROM order.xlsx

      (ooxml, embedded labels, table is Sheet1);

       

      LEFT JOIN (LAUNCH)

      LOAD key1,

          key2,

          key3,

          key4,

          dimA

          dimB,

          dimC,

          dimD,

          dimE,

          dimF

      FROM master.xls

      (biff, embedded labels, table is Sheet1);

       

       

      Thank you