topic Re: How to select specific lines in QlikView

How to select specific lines

Mon, 26 Nov 2012 07:46:22 GMT

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

Re: How to select specific lines

Gysbert_Wassenaar — Mon, 26 Nov 2012 10:31:22 GMT

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

Invoice:

Load *

from ....

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

Re: How to select specific lines

Mon, 26 Nov 2012 12:30:05 GMT

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

Re: How to select specific lines

Gysbert_Wassenaar — Mon, 26 Nov 2012 13:39:00 GMT

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?

Re: How to select specific lines

Mon, 26 Nov 2012 14:04:58 GMT

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

Re: How to select specific lines

Gysbert_Wassenaar — Mon, 26 Nov 2012 14:49:34 GMT

Try this then:

Combined:

load *, ORDNUMBER&Item as Key

FROM .....;

Concatenate

LOAD *

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

drop Field Key;

Re: How to select specific lines

Mon, 26 Nov 2012 15:06:44 GMT

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

Re: How to select specific lines

Gysbert_Wassenaar — Mon, 26 Nov 2012 15:23:02 GMT

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

Re: How to select specific lines

Mon, 26 Nov 2012 15:45:40 GMT

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;

Re: How to select specific lines

Gysbert_Wassenaar — Mon, 26 Nov 2012 15:52:31 GMT

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

Re: How to select specific lines

Tue, 27 Nov 2012 07:14:09 GMT

Hi Gysbert

It worked. Thank you for all your help. Much appreciated.

kind regards

Nayan

Re: How to select specific lines

Tue, 27 Nov 2012 08:31:00 GMT

Hi Gysbert

Just one query, the actual data that i am working on, the ORDNUMBER and ITEM are in two seperate tables. They have a common link field called UNIQINV. How do I apply this in the scripting you have given me.

kind regards

Nayan