2 Replies Latest reply: Jan 15, 2016 8:26 AM by Anton Aleksandrov RSS

    add lines to file

    gidon david

      Hi

      my customer get a file with list of order

      in the line there is a PART

      if this part exist is a table , we need to add another line  .with ADDPART

      the table include PART and ADDPART with the price .

       

      enclosed is excel file with 2 sheets

      first sheet

      DATAIN

      includes the list of orders

      and table with PART AND ADDPART

       

      second sheet is DATAOUT

      is the table requested

      the yellow lines are PART that have been  added to the original order file

       

      how can I make it happen

       

      thanks

      gidon

        • Re: add lines to file
          Anton Aleksandrov

          Try this script:


          OriginalTable:
          LOAD ORD,
          CUST,
          PART,
          QTY,
          PRICE
          FROM
          ADDLINES.xlsx
          (
          ooxml, embedded labels, table is DATAIN);


          NoConcatenate
          AddTable:
          LOAD
          PART1 as PART_ForJoin
          ADDPART as PART,
          PRICE1 as PRICE
          FROM
          ADDLINES.xlsx
          (
          ooxml, embedded labels, table is DATAIN)
          Where Exists(PART,PART1);

          LEFT JOIN(AddTable)
          LOAD
          ORD,
          CUST,
          PART as PART_ForJoin,
          QTY
          Resident OriginalTable;

          Concatenate(OriginalTable)
          LOAD
          *
          Resident AddTable;

          DROP TABLE AddTable;

          • Re: add lines to file
            bobbyraj santhiogu

            Hi,

            First load your two tables:

             

            FACTS:

            LOAD

              ORD,

              CUST,

              PART,

              QTY,

              PRICE

            FROM file.xls;

             

            PART_ADDPART:

            LOAD

              PART as PART_OUT,

              ADDPART as ADDPART_OUT,

              PRICE as PRICE_OUT

            FROM file xls;

             

            TEMP:

            LOAD

              ORD,

              CUST,

              PART,

              QTY

            RESIDENT FACTS

            Where exists (PART_OUT,PART) ;

             

            LEFT JOIN(TEMP)

            LOAD

              PART_OUT as PART,

              ADDPART_OUT,

              PRICE_OUT

            RESIDENT PART_ADDPART;

             

            DROP TABLE PART_ADDPART;

             

             

            Concatenate (FACTS)

            LOAD

              ORD,

              CUST,

              QTY,

              ADDPART_OUT as PART,

              PRICE_OUT as PRICE

            RESIDENT TEMP;

             

            DROP TABLE TEMP;

             

            Then your table FACTS will contain all datas (with the new lines)