Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
can you post some data (and the result you want)?
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;
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
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.
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
It works great! Thanks
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;
Excellent