How to select specific lines

[#1]

Hi

I have 2 tables , one is ORDER and another for INVOICE.  Once an order is invoiced, the lines in the "Invoice" Table replaces the lines in the "Order" Table.

However i want to look at orders that are not invoiced at all, hence I have combined the 2 tables , by concatenating the "Order" table to the "Invoice" table and thereafter dropping the "Orders" table as shown below.

Concatenate ([INVOICE])

Load * Resident [ORDER];

drop table [ORDER];

When an order is invoiced , it generates an invoice number, otherwise invoice number  its shown as "****NEW***. 

• When an item in the order is fully invoiced, the line in the order table is replaced with the line in the invoice table.
• However if an item is partially invoiced or not invoiced, then same item appears twice.

I only want to show the line that appears in the invoice table and the line in the order table to fall off. 

How do i do this in the scripting.

See attached excel .  Those lines that are highlighted I want them to fall away. 

kind regards

Nayan

[Gysbert_Wassenaar]

Doesn't adding a where clause to the load of the Invoice table do what you want?

Invoice:

Load *

from ....

where INVNUMBER <> '*** NEW ***' ;

[#3]

Hi Gysbert

The ***NEW*** comes from the order table.  I dont want to exclude ***NEW*** because I also want to view new  orders outstanding.

Below is the order table, invoice table and combined table. In the combined table i want the duplicate entry remove.

Sorry for showing the table below, as i cannot attached the excel file.

Many thanks

kind regards

Nayan

ORDER TABLE
ORDNUMBER	INVDATE	INVNUMBER	Item	Status	Qty Ordered	Qty Invoiced	Qty BackOrder
ORD-001	20121105	*** NEW ***	A	Outstanding	500	0	0
ORD-001	20121105	*** NEW ***	B	Outstanding	100	0	0
ORD-001	20121105	*** NEW ***	C	Outstanding	12	0	0
ORD-001	20121105	*** NEW ***	D	Outstanding	12	0	0
ORD-001	20121105	*** NEW ***	E	Outstanding	5	0	0
ORD-001	20121105	*** NEW ***	F	Outstanding	5	0	0
ORD-001	20121105	*** NEW ***	G	Outstanding	60	0	0
ORD-001	20121105	*** NEW ***	H	Outstanding	60	0	0
ORD-001	20121105	*** NEW ***	I	Outstanding	20	0	0
ORD-001	20121105	*** NEW ***	J	Outstanding	120	0	0
ORD-001	20121105	*** NEW ***	K	Outstanding	108	0	0
ORD-001	20121105	*** NEW ***	L	Outstanding	600	0	0
ORD-001	20121105	*** NEW ***	M	Outstanding	14	0	0
INVOICE TABLE
ORDNUMBER	INVDATE	INVNUMBER	Item	Status	Qty Ordered	Qty Invoiced	Qty BackOrder
ORD-001	20121105	INV-002	A	Partially Invoiced	500	306	194
ORD-001	20121105	INV-002	B	Outstanding	100	0	100
ORD-001	20121105	INV-002	C	Complete	12	12	0
ORD-001	20121105	INV-002	D	Outstanding	12	0	12
ORD-001	20121105	INV-002	E	Outstanding	5	0	5
ORD-001	20121105	INV-002	F	Outstanding	5	0	5
ORD-001	20121105	INV-002	G	Complete	60	60	0
ORD-001	20121105	INV-002	H	Outstanding	60	0	60
ORD-001	20121105	INV-002	I	Outstanding	20	0	20
ORD-001	20121105	INV-002	J	Complete	120	120	0
ORD-001	20121105	INV-002	K	Complete	108	108	0
ORD-001	20121105	INV-002	L	Outstanding	600	0	600
ORD-001	20121105	INV-002	M	Complete	14	14	0
COMBINED TABLE
ORDNUMBER	INVDATE	INVNUMBER	Item	Status	Qty Ordered	Qty Invoiced	Qty BackOrder
ORD-001	20121105	INV-002	A	Partially Invoiced	500	306	194
ORD-001	20121105	*** NEW ***	A	Outstanding	194	0	194
ORD-001	20121105	INV-002	B	Outstanding	100	0	100
ORD-001	20121105	*** NEW ***	B	Outstanding	100	0	100
ORD-001	20121105	INV-002	C	Complete	12	12	0
ORD-001	20121105	*** NEW ***	D	Outstanding	12	0	12
ORD-001	20121105	INV-002	D	Outstanding	12	0	12
ORD-001	20121105	*** NEW ***	E	Outstanding	5	0	5
ORD-001	20121105	INV-002	E	Outstanding	5	0	5
ORD-001	20121105	*** NEW ***	F	Outstanding	5	0	5
ORD-001	20121105	INV-002	F	Outstanding	5	0	5
ORD-001	20121105	INV-002	G	Complete	60	60	0
ORD-001	20121105	INV-002	H	Outstanding	60	0	60
ORD-001	20121105	*** NEW ***	H	Outstanding	60	0	60
ORD-001	20121105	INV-002	I	Outstanding	20	0	20
ORD-001	20121105	*** NEW ***	I	Outstanding	20	0	20
ORD-001	20121105	INV-002	J	Complete	120	120	0
ORD-001	20121105	INV-002	K	Complete	108	108	0
ORD-001	20121105	INV-002	L	Outstanding	600	0	600
ORD-001	20121105	*** NEW ***	L	Outstanding	600	0	600
ORD-001	20121105	INV-002	M	Complete	14	14	0

[Gysbert_Wassenaar]

I think I figured out what you are trying to do. It's a bit more complicated than just selecting some lines. Can you check the attached qvw to see if it does what you want?

[#5]

Hi Gysbert

Thanks for your reply. Not quite.

In this example , the lines containing invoice number  " ***NEW*** " should not appear.  The ideal result for the combined Table 3 below is below .   Coincidentally, in this example  it is a same as the invoice table.

Lets just say there is an new order (say, ORD-002) , see Table 1 below: 

Now, this order has not been invoiced as all, then the result shuold be as follows:  See Table 2: . 

Please note that the "Combined Table" is my results table.  The answer i'm looking for .

kind regards

Nayan

Table 1:  (Order Table)

ORDNUMBER	INVDATE	INVNUMBER	Item	Status	Qty Ordered	Qty Invoiced	Qty BackOrder
ORD-002	20121105	*** NEW ***	X	Outstanding	800	0	800
ORD-002	20121105	*** NEW ***	Y	Outstanding	60	0	60
ORD-002	20121105	*** NEW ***	Z	Outstanding	100	0	100

Table 2:  (Combined Table)

ORDNUMBER	INVDATE	INVNUMBER	Item	Status	Qty Ordered	Qty Invoiced	Qty BackOrder
ORD-002	20121105	*** NEW ***	X	Outstanding	800	0	800
ORD-002	20121105	*** NEW ***	Y	Outstanding	60	0	60
ORD-002	20121105	*** NEW ***	Z	Outstanding	100	0	100

Table 3: (Combined Table)

ORDNUMBER	INVDATE	INVNUMBER	Item	Status	Qty Ordered	Qty Invoiced	Qty BackOrder
ORD-001	20121105	INV-002	A	Partially Invoiced	500	306	194
ORD-001	20121105	INV-002	B	Outstanding	100	0	100
ORD-001	20121105	INV-002	C	Complete	12	12	0
ORD-001	20121105	INV-002	D	Outstanding	12	0	12
ORD-001	20121105	INV-002	E	Outstanding	5	0	5
ORD-001	20121105	INV-002	F	Outstanding	5	0	5
ORD-001	20121105	INV-002	G	Complete	60	60	0
ORD-001	20121105	INV-002	H	Outstanding	60	0	60
ORD-001	20121105	INV-002	I	Outstanding	20	0	20
ORD-001	20121105	INV-002	J	Complete	120	120	0
ORD-001	20121105	INV-002	K	Complete	108	108	0
ORD-001	20121105	INV-002	L	Outstanding	600	0	600
ORD-001	20121105	INV-002	M	Complete	14	14	0

[Gysbert_Wassenaar]

Try this then:

Combined:

load *,  ORDNUMBER&Item as Key

FROM .....;

Concatenate

LOAD *

FROM ....  where not exists(Key,ORDNUMBER&Item);

drop Field Key;

[#7]

Hi Gysbert

When i type in concatenate, it has a red underline .  By the way, im new at Qlikview and sure on the scripting.

kind regaqrds

Nayan

[Gysbert_Wassenaar]

Keep typing. As soon as you type LOAD after concatenate the red underline will disappear.

[#9]

Hi Gysbert

Thanks .  I've copied the file onto my desktop  .   Split the invoice table and order table into seperate sheets. 

However I can't seem to get the script working.  Below is the scripting.  Can you tell me where I am going wrong. 

Thanks .

By the way, im on my way hoem, will have a look tomorrow morning.

kind regards

Nayan

Temp:
LOAD ORDNUMBER&Item as Key,
[Qty BackOrder] as QBO
FROM
C:\Users\nayan.lalla\Desktop\comm67010.xlsx
(biff, embedded labels, table is [Invoice])
where Status <> 'Complete';

left join
LOAD
     ORDNUMBER&Item as Key,
     ORDNUMBER,
     INVDATE,
     INVNUMBER,
     Item,
     Status,
     [Qty Ordered],
     [Qty Invoiced],
     [Qty BackOrder]
FROM
C:\Users\nayan.lalla\Desktop\comm67010.xlsx
(biff, embedded labels, table is [Order]);

Combined:
load
     ORDNUMBER,
     ORDNUMBER&Item as Key,
     INVDATE,
     INVNUMBER,
     Item,
     Status,
     QBO as [Qty Ordered],
     [Qty Invoiced],
     QBO as [Qty BackOrder]
Resident Temp;    

drop table Temp;

load *
FROM
C:\Users\nayan.lalla\Desktop\comm67010.xlsx
(biff, embedded labels, table is [Invoice]);

Concatenate

LOAD *
FROM
C:\Users\nayan.lalla\Desktop\comm67010.xlsx
(biff, embedded labels, table is [Invoice]);

Drop Field Key;

[Gysbert_Wassenaar · Accepted solution]

Remove all of the script and replace it with what I posted in my previous post. See attached files.