topic Re: Exclude exception rows - Options to handle ? in QlikView

Exclude exception rows - Options to handle ?

umashankarus — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I have an input data table as below (Note: there are tens of columns in the real data table)

ID1	CODE1	UOM1	ID2	CODE2	UOM2
A	0000002598	CASE	B	0000002598	CASE
A	0000002598	CASE	B	0000976484	EACH
A	0000003818	CASE	B	0000003818	CASE
A	0000003818	CASE	B	0000976496	EACH
A	0000023744	CASE	B	0000023744	CASE
A	0000315051	CASE	B	0000999321	EACH

If Number of occurence of CODE1 = 1, then it is an exception

Within the document, I am able to list out and display the exceptions (the last 2 rows)

ID1	CODE1	UOM1	ID2	CODE2	UOM2
A	0000023744	CASE	B	0000023744	CASE
A	0000315051	CASE	B	0000999321	EACH

But I also wanted the exceptions to be excluded and the original table (without exception records) should be displayed as below (the first 4 rows)

ID1	CODE1	UOM1	ID2	CODE2	UOM2
A	0000002598	CASE	B	0000002598	CASE
A	0000002598	CASE	B	0000976484	EACH
A	0000003818	CASE	B	0000003818	CASE
A	0000003818	CASE	B	0000976496	EACH

Can this be handled in the load script by using 'group by' only ?

If not within the load script, can this be handled at the time of document open through trigger ?

Pl provide suggestions and details

Thanks,

Umashankar

Re: Exclude exception rows - Options to handle ?

varshavig12 — Thu, 30 Mar 2017 04:09:46 GMT

May be something like this:,,

t1:

LOAD * INLINE [

ID1, CODE1, UOM1, ID2, CODE2, UOM2

A, 0000002598, CASE, B, 0000002598, CASE

A, 0000002598, CASE, B, 0000976484, EACH

A, 0000003818, CASE, B, 0000003818, CASE

A, 0000003818, CASE, B, 0000976496, EACH

A, 0000023744, CASE, B, 0000023744, CASE

A, 0000315051, CASE, B, 0000999321, EACH

];

t2:

left join load CODE1,count(ID1) as c

Resident t1

group by CODE1;

right join load *

Resident t1

where c>1;

Re: Exclude exception rows - Options to handle ?

Kushal_Chawda — Thu, 30 Mar 2017 04:58:21 GMT

t1:

LOAD * INLINE [

ID1, CODE1, UOM1, ID2, CODE2, UOM2

A, 0000002598, CASE, B, 0000002598, CASE

A, 0000002598, CASE, B, 0000976484, EACH

A, 0000003818, CASE, B, 0000003818, CASE

A, 0000003818, CASE, B, 0000976496, EACH

A, 0000023744, CASE, B, 0000023744, CASE

A, 0000315051, CASE, B, 0000999321, EACH

];

t2:

LOAD ID1,

CODE1,

Count(CODE1) as Count,

UOM1,

ID2,

CODE2,

UOM2

resident t1

group by

ID1,

UOM1,

ID2,

CODE2,

UOM2;

drop table t1;

t3:

noconcatenate

LOAD *

resident t2

where Count>1;

drop table t2;

Re: Exclude exception rows - Options to handle ?

umashankarus — Thu, 30 Mar 2017 13:15:49 GMT

Thanks for the reply

I am also looking for other options other than using 'group by'

Re: Exclude exception rows - Options to handle ?

umashankarus — Thu, 30 Mar 2017 13:15:57 GMT

Thanks for the reply

I am also looking for other options other than using 'group by'