topic Re: add lines to file in QlikView

add lines to file

gidon500 — Fri, 15 Jan 2016 12:51:20 GMT

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

asgardd2 — Fri, 15 Jan 2016 13:26:52 GMT

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

Anonymous — Fri, 15 Jan 2016 14:19:40 GMT

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)