ROW_NUMBER with more than one partition

[#1]

Hello,

I have the following problem: I must take the first row of a sub-group of rows.

In SQL I use to proceed in this way:

SELECT

     field

FROM

     (

          SELECT

               field

                  ROW_NUMBER() OVER (PARTITION BY a,b,c ORDER BY a) as row

          FROM

               table

     )

WHERE

     row = 1

note that I have more than one partition( a,b,c).

How can I implement this in Qlikview?

Thanks in advance.

[maxgro]

can you post some data (and the result you want)?

[maxgro]

I think (try to guess),

if your data is in Source table and the 4 fields of that table are a, b, c, field

Table:

load field where flag = 1;

NoConcatenate load

a, b, c, field,

if(a<>peek(a) or b<>peek(b) or c <>peek(c), 1) as flag

resident Source

order by a,b,c;

[#4]

Hi,

1) Either you do your current SQL command (it should be accepted by QV because QV does not interpret the SQL command: it just gives it to the OLE/ODBC driver)

LOAD field  ....;

SQL ....

2) try with a group by a,b,c and a function like FirstValue(Field), FirstSortedValue()

temp1:

LOAD complete;

LOAD FirstValue(Field) as Field

resident Temp1

group by a, b, c;

Fabrice

[#5]

I have a table like this:

ID	ID_Campagna	Anno	Mese	ID_Progetto_HR	OPERATORE	COSTO	Data
1	21	2014	1	1001	2001	300	2014-01-23 00:00:00.000
2	21	2014	1	1001	2001	250	2014-01-22 00:00:00.000
3	21	2014	1	1001	2001	150	2014-01-15 00:00:00.000
4	21	2014	1	1001	2001	260	2014-01-12 00:00:00.000
5	21	2014	1	1001	2005	540	2014-01-23 00:00:00.000
6	21	2014	1	1001	2005	230	2014-01-21 00:00:00.000
7	21	2014	1	1047	2005	640	2014-01-31 00:00:00.000
8	21	2014	1	1047	2005	350	2014-01-30 00:00:00.000
9	21	2014	1	1047	2005	210	2014-01-29 00:00:00.000
10	64	2014	1	1003	2028	440	2014-01-13 00:00:00.000
11	64	2014	1	1003	2028	350	2014-01-10 00:00:00.000
12	64	2014	1	1003	2028	120	2014-01-09 00:00:00.000

I would like to take only the first row for each "Group by" Anno, Mese, ID_Progetto_HR, OPERATORE, ID_Campagna. ordered by Data DESC.

In this case I would only take the following record id:

1, 5,7,10

In SQL I would have done like this:

SELECT b.ID_Campagna

,b.ANNO

,b.MESE

,b.ID_Progetto_HR

,b.OPERATORE

,b.[COSTO]

FROM

(

SELECT

ttoa.ID_Campagna

,YEAR(ttoa.[DATA A]) AS ANNO

,MONTH(ttoa.[DATA A]) AS MESE

,ttoa.ID_Progetto_HR

,ttoa.OPERATORE

,ttoa.[COSTO]

,ttoa.[DATA]

,ROW_NUMBER() OVER(PARTITION BY YEAR(ttoa.[DATA A]),MONTH(ttoa.[DATA A]),ttoa.ID_Progetto_HR,ttoa.OPERATORE,ttoa.ID_Campagna ORDER BY ttoa.[DATA A] DESC) AS Riga

FROM TracciatiOutput_Aggregazione ttoa

) b

WHERE b.Riga = 1

I hope I've been clearer now.

Thanks again.

[#6 · Accepted solution]

Did you try ?

LOAD ID_Campagna, Anno, Mese, ID_Progetto_HR, Operatore, FirstValue(Costo), FirstValue(Data)

Resident YourOrderTable

Group By ID_Campagna, Anno, Mese, ID_Progetto_HR, Operatore;

If FirstValue() does not suit you, you may look at FirstSortedValue() function that will return the first value (1st arg), but sorted on a criteria given as 2nd argument.

Fabrice

[#7]

It works great! Thanks

[maxgro]

RESULT

ID_Campagna	Anno	Mese	ID_Progetto_HR	OPERATORE	Data	ID	COSTO
21	2014	1	1001	2001	2014-01-23 00:00:00.000	1	300
21	2014	1	1001	2005	2014-01-23 00:00:00.000	5	540
21	2014	1	1047	2005	2014-01-31 00:00:00.000	7	640
64	2014	1	1003	2028	2014-01-13 00:00:00.000	10	440

SCRIPT

using samelogic of my previous post

SET ThousandSep='.';

SET DecimalSep=',';

SET MoneyThousandSep='.';

SET MoneyDecimalSep=',';

SET MoneyFormat='€ #.##0,00;-€ #.##0,00';

SET TimeFormat='hh:mm:ss';

SET DateFormat='DD/MM/YYYY';

SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';

SET MonthNames='gen;feb;mar;apr;mag;giu;lug;ago;set;ott;nov;dic';

SET DayNames='lun;mar;mer;gio;ven;sab;dom';

Source:

LOAD ID,

     ID_Campagna,

     Anno,

     Mese,

     ID_Progetto_HR,

     OPERATORE,

     COSTO,

     Data

FROM

[http://community.qlik.com/message/482677?et=watches.email.thread#482677]

(html, codepage is 1252, embedded labels, table is @1);

Table:

load *

where flag = 1;

NoConcatenate

load

  ID,

     ID_Campagna,

     Anno,

     Mese,

     ID_Progetto_HR,

     OPERATORE,

     COSTO,

     Data,

  if(Anno<>peek(Anno) or Mese<>peek(Mese) or ID_Progetto_HR <>peek(ID_Progetto_HR)

  or OPERATORE<>peek(OPERATORE) or ID_Campagna<>peek(ID_Campagna), 1) as flag

resident Source

ORDER BY  ID_Campagna,

     Anno,

     Mese,

     ID_Progetto_HR,

     OPERATORE,

     Data desc;

drop table Source;   

DROP field flag;

[#9]

Excellent