topic Re: How to Remove Duplicates based on One Column in QlikView

How to Remove Duplicates based on One Column

Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

I have a query, I have a table which is shown below:

Input:

Row_num	Text
1	Jan
2	Feb
1	adr
1	asd
2	wer
3	Dec
4	Mon

Here I want the unique value based first in first out on the Row_num fields:

Output:

Row_num	Text
1	Jan
2	Feb
3	Dec
4	Mon

Thanks & Regards,

Pawan

Re: How to Remove Duplicates based on One Column

sunny_talwar — Wed, 07 Dec 2016 16:07:11 GMT

May be this:

Table:

LOAD *

Where NOT Exists(Row_num);

LOAD * INLINE [

Row_num, Text

1, Jan

2, Feb

1, adr

1, asd

2, wer

3, Dec

4, Mon

];

Re: How to Remove Duplicates based on One Column

tamilarasu — Wed, 07 Dec 2016 16:31:48 GMT

Or you can try this as well.

Table:

LOAD Row_num, FirstValue(Text) as Text Group by Row_num;

LOAD * INLINE [

Row_num, Text

1, Jan

2, Feb

1, adr

1, asd

2, wer

3, Dec

4, Mon

];

Re: How to Remove Duplicates based on One Column

Thu, 08 Dec 2016 04:29:42 GMT

Hi Both,

Thanks for your answer. Which is correct but not in my case.

I think I was wrong to explaining the question. Let me explain it fully:

Previously I was creating the report in excel and fetch the data from one table to another table using Vlookup function so I will get the first record from another table (if there is any duplicate records in another table).

But in Qlikview I am using left join so in that case I am getting multiple result because there is multiple entries in the right table (logically it is correct) but I want one record only which is comes first in the Right table. So here is an example:

LOAD * INLINE [

Row_num, Text1,Text2,Text3,Text4,Text5

1, a1,b1,c1,d1,e1

2, a2,b2,c2,d2,e2

3, a3,b3,c3,d3,e3

4, a4,b4,c4,d4,e4

];

Left Join

LOAD * INLINE [

Row_num, Text6,Text7,Text8,Text9,Text10

1, Jan,Feb,Mar,Apr,May

2, Feb,Mon,Tue,Sat,Sun

3, adr,Fri,Qrt,wer,tyh

4, asd,sdf,wer,dsf,try

2, wer,ert,try,dfg,ert

3, Dec,dfg,rty,sdf,ghg

4, Mon,tyu,sdf,ert,fdg

];

Here I want all the fields from both the table.

Please let me know if you have any concern.

Thanks & Regards,

Pawan

Re: How to Remove Duplicates based on One Column

tamilarasu — Thu, 08 Dec 2016 15:08:22 GMT

Hi Pawan,

Try this,

Table:
LOAD * INLINE [
Row_num, Text6,Text7,Text8,Text9,Text10
1, Jan,Feb,Mar,Apr,May
2, Feb,Mon,Tue,Sat,Sun
3, adr,Fri,Qrt,wer,tyh
4, asd,sdf,wer,dsf,try
2, wer,ert,try,dfg,ert
3, Dec,dfg,rty,sdf,ghg
4, Mon,tyu,sdf,ert,fdg
]

Where NOT Exists(Row_num);

Right Join (Table)

LOAD * INLINE [
Row_num, Text1,Text2,Text3,Text4,Text5
1, a1,b1,c1,d1,e1
2, a2,b2,c2,d2,e2
3, a3,b3,c3,d3,e3
4, a4,b4,c4,d4,e4
];

Re: How to Remove Duplicates based on One Column

rupamjyotidas — Thu, 08 Dec 2016 16:03:00 GMT

TesT:

LOAD * INLINE [

Row_num, Text1,Text2,Text3,Text4,Text5

1, a1,b1,c1,d1,e1

2, a2,b2,c2,d2,e2

3, a3,b3,c3,d3,e3

4, a4,b4,c4,d4,e4

];

Left Join

LOAD * INLINE [

Row_num, Text6,Text7,Text8,Text9,Text10

1, Jan,Feb,Mar,Apr,May

2, Feb,Mon,Tue,Sat,Sun

3, adr,Fri,Qrt,wer,tyh

4, asd,sdf,wer,dsf,try

2, wer,ert,try,dfg,ert

3, Dec,dfg,rty,sdf,ghg

4, Mon,tyu,sdf,ert,fdg

];

NoConcatenate

Load * Resident TesT Where Peek(Row_num)<>Row_num;

Drop Table TesT;

Re: How to Remove Duplicates based on One Column

Fri, 09 Dec 2016 03:59:49 GMT

Both the solutions are work exactly as I want.

Thanks for giving the solution.

Regards,

Pawan