topic Re: Get distinct value in QlikView

Get distinct value

sunilkumarqv — Tue, 03 Mar 2015 13:04:00 GMT

Table :

Inline

[

Date,Key

1/2/2015,A

1/2/2015,B

2/3/2015,A

2/3/2015,B

3/3/2015,c

] ;

Need to get o/p: Date,Key

3/3/2015,C

Re: Get distinct value

sunny_talwar — Tue, 03 Mar 2015 13:06:48 GMT

Is the idea to get the Date and Key where Date is maximum? or get Date and Key which are only showing up once in the database?

Best,

Re: Get distinct value

PrashantSangle — Tue, 03 Mar 2015 13:07:22 GMT

Hi,

USe max() or FirstSortedValue()

Regards

Re: Get distinct value

JonnyPoole — Tue, 03 Mar 2015 13:30:42 GMT

I agree there are multiple criteria that could result in 3/3/2015,C returned . Please define what you need to return. Also you used a capital 'C' and the data is lower case 'c' . Please confirm that too.

Re: Get distinct value

stabben23 — Tue, 03 Mar 2015 15:29:33 GMT

New_Table:

LOAD

Date,

Upper(Key) as Key

Resident Table;

Drop Table;

or?

Re: Get distinct value

robert_mika — Tue, 03 Mar 2015 15:33:19 GMT

Table1 :

load * Inline

[

Date,Key

1/2/2015,A

1/2/2015,B

2/3/2015,A

2/3/2015,B

3/3/2015,c

] ;

join(Table1)

load

Key,

count(Key) as CountKey

Resident Table1

group by Key;

final:

load

Date,

Key

resident Table1

where CountKey=1;

drop table Table1;