add lines to file

[gidon500]

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

[asgardd2 · Accepted solution]

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;

[Anonymous]

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)