Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

gidon500
Contributor II

add lines to file

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

1 Solution

Accepted Solutions
asgardd2
Contributor III

Re: add lines to file

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;

2 Replies
asgardd2
Contributor III

Re: add lines to file

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;

sbobbyraj
Contributor III

Re: add lines to file

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)

Community Browser