How to Remove Duplicates based on One Column

[#1]

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

[sunny_talwar]

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

];

[tamilarasu]

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

];

[#4]

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

[tamilarasu]

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
];

[rupamjyotidas · Accepted solution]

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;

[#7]

Both the solutions are work exactly as I want.

Thanks for giving the solution.

Regards,

Pawan