Qlik Community

Ask a Question

QlikView Administration

Discussion Board for collaboration on QlikView Management.

Announcements
Join us at the Cloud Data and Analytics Tour! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
venkatbza
Creator
Creator

How to remove the single values in the table

Hi Friends,

I have a table which contains two columns order number and line item number. Each order number can contain multiple line items numbers and few are having single line items number also. Here my requirement is i have to remove if order number having single line item number from the table. below is the sample data.

Sample data:

Order numberLine item number
100011
100012
100013
100021
100022
100023
100024
100031
100041
100051
100052
100061

 

Output expected:

Order numberLine item number
100011
100012
100013
100021
100022
100023
100024
100051
100052
1 Solution

Accepted Solutions
asinha1991
Creator III
Creator III

A:

Load [Order number] where cnt >1;

Load [Order number],  count(distinct Line item number) as cnt from urtable group by  [Order number];

 

inner join(A)

Load * from urtable;

 

 

View solution in original post

4 Replies
dilipranjith
Partner
Partner

You want to do this in script or only for a table?

venkatbza
Creator
Creator
Author

Hi,

Anything is fine for me. If possible give the solution in both ways.

 

Thanks

asinha1991
Creator III
Creator III

A:

Load [Order number] where cnt >1;

Load [Order number],  count(distinct Line item number) as cnt from urtable group by  [Order number];

 

inner join(A)

Load * from urtable;

 

 

View solution in original post

dilipranjith
Partner
Partner

i see asinhya has give script to remove it during load.

use below approach to flag it so that you do it in Ui as well. (where MoreThan1LineItemFlag=1)

Orders:
load * inline [

Order number,	Line item number
10001,	1
10001,	2
10001,	3
10002,	1
10002,	2
10002,	3
10002,	4
10003,	1
10004,	1
10005,	1
10005,	2
10006,	1
];

Count:
load [Order number],
 1 as MoreThan1LineItemFlag
 where Count>1
;
load [Order number]
,count([Line item number]) as Count
resident Orders
group by [Order number]
;