topic Re: Largest unique value in QlikView

Largest unique value

johanru1 — Mon, 13 Nov 2017 10:07:26 GMT

Hi,

I am trying to pull the largest value from one column based on a unique value in a different column.

1. Column A contains order numbers (need highest number)

2. Column B contains a serial number that I need a unique value from.

A	B
1	222
2	222
3	333
4	111
5	333
6	222

I want the output to be something like this:

A	B
4	111
6	222
5	333

Re: Largest unique value

sunny_talwar — Mon, 13 Nov 2017 16:15:31 GMT

May be this

LOAD B,

Max(A) as A

FROM ....

Group By B;

Re: Largest unique value

its_anandrjs — Mon, 13 Nov 2017 16:54:38 GMT

May be you need the order also then try this.

Src:

LOAD * Inline [

A, B

1, 222

2, 222

3, 333

4, 111

5, 333

6, 222 ];

NoConcatenate

New:

LOAD

B,Max(A) as A

Resident Src

Group By B Order By B asc;

DROP Table Src;

Re: Largest unique value

Monicalingan — Mon, 13 Nov 2017 17:55:19 GMT

Hi Johan,

Use below code,

Inline:

LOAD * INLINE [

A, B

1, 222

2, 222

3, 333

4, 111

5, 333

6, 222

];

Inner join(Inline)

Load B,A

Resident Inline

where if(B=peek(B),peek("Row Number")+1,1)=1

Order by B asc,A desc

Hope it helps

Thanks,

Monica

Re: Largest unique value

johanru1 — Tue, 14 Nov 2017 07:08:21 GMT

Hi Moica, thank you for the reply.

I seem to have forgotten to mention that the data is on a MSSQL server.

Guessing that is why I got the following error:

ErrorSource: Microsoft OLE DB Provider for SQL Server, ErrorMsg: 'peek' is not a recognized built-in function name.

Re: Largest unique value

johanru1 — Tue, 14 Nov 2017 07:44:52 GMT

This seems to have worked as expected

The issue now, is that the A column should only retrieve data based on the matches it find in a different table.

A.Alpha should validate it's data from A.Bravo.

Thanks in advance

Re: Largest unique value

Monicalingan — Thu, 16 Nov 2017 04:18:47 GMT

Largestuniquevalues:

LOAD A,

SQL SELECT A,

FROM MLINGAN.dbo."tbl_largestuniquevalues";

Inner join(Largestuniquevalues)

Load B,A

Resident Largestuniquevalues

where if(B=peek(B),peek("Row Number")+1,1)=1

Order by B asc,A desc;