Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Good morning
I need to select only one record from a Qlik Table with the max value for a specific field
I have this table with three rows about Lotto 0100017763
I need to generate the table only the row with Max value of field "Misura" (in this case 19)
I've tried with this script
[PREMI]:
LOAD
ID_TES_PREMI,
max(ID_COD_MIS_PREMI)
RESIDENT PREMI_TMP_2
GROUP BY ID_TES_PREMI;
But I have only this result and I need also other fileds
If I use this script
[PREMI]:
LOAD
ID_TES_PREMI,
LastValue(ID_LOTTO_PREMI) AS ID_LOTTO_PREMI2,
LastValue(ID_CRE_PREMI) AS ID_CRE_PREMI2,
LastValue(ID_COD_MIS_PREMI) AS ID_COD_MIS_PREMI2,
LastValue(ID_RES_PREMI) as ID_RES_PREMI2,
LastValue(ID_RES_CAUSA) AS ID_RES_CAUSA2,
LastValue(ID_FORNITORE_PREMI) as ID_FORNITORE_PREMI2,
LastValue(ID_COD_CAU_PREMI) AS ID_COD_CAU_PREMI2,
LastValue(DATA_CRE_MIS_PREMI) AS DATA_CRE_MIS_PREMI2,
LastValue(ID_ART_PREMI) AS ID_ART_PREMI2,
LastValue(CONTA_PREMI) AS CONTA_PREMI2,
LastValue(ID_PLANT_PREMI) AS ID_PLANT_PREMI2,
LastValue(QTA_RECLAMO_PREMI) AS QTA_RECLAMO_PREMI2,
LastValue(ANNO_PREMI) AS ANNO_PREMI2,
LastValue(MESE_PREMI) AS MESE_PREMI2,
LastValue(KeyLinkPremi) AS KeyLinkPremi2
RESIDENT PREMI_TMP_2
GROUP BY ID_TES_PREMI;
the result is
But it's wrong because I have the Misura 07 (and not 19) and Data creazione 12/01/2017 and not 20/06/2017 as for "misura" 19.
I try also with
[PREMI]:
LOAD Distinct
ID_TES_PREMI,
ID_LOTTO_PREMI AS ID_LOTTO_PREMI2,
ID_CRE_PREMI AS ID_CRE_PREMI2,
ID_COD_MIS_PREMI AS ID_COD_MIS_PREMI2,
ID_RES_PREMI as ID_RES_PREMI2,
ID_RES_CAUSA AS ID_RES_CAUSA2,
ID_FORNITORE_PREMI as ID_FORNITORE_PREMI2,
ID_COD_CAU_PREMI AS ID_COD_CAU_PREMI2,
DATA_CRE_MIS_PREMI AS DATA_CRE_MIS_PREMI2,
ID_ART_PREMI AS ID_ART_PREMI2,
CONTA_PREMI AS CONTA_PREMI2,
ID_PLANT_PREMI AS ID_PLANT_PREMI2,
QTA_RECLAMO_PREMI AS QTA_RECLAMO_PREMI2,
ANNO_PREMI AS ANNO_PREMI2,
MESE_PREMI AS MESE_PREMI2,
KeyLinkPremi AS KeyLinkPremi2
RESIDENT PREMI_TMP_2
ORDER BY ID_COD_MIS_PREMI DESC;
but the result is
Some can help me.
Thanks
cristian
You have to have a Group By clause.
so the end would would be
Load
ID_TES_PREMI,
max(ID_COD_MIS_PREMI) as Max_Cod_Mis
Resident PREMI_TMP_2
Group By ID_TES_PREMI;
May be try this?
[PREMI]:
LOAD
ID_TES_PREMI,
FirstSortedValue(ID_COD_MIS_PREMI, -ID_TES_PREMI) as ID_COD_MIS_PREMI
RESIDENT PREMI_TMP_2
GROUP BY ID_TES_PREMI;
Noconcatenate
Load *
resident PREMI_TMP_2;
Drop table PREMI_TMP_2;
inner join Load Lotto,
Max(Misura) as MaxMisura
resident PREMI_TMP_2;
Hi Anil
in this way I have only the field
ID_TES_PREMI
ID_COD_MIS_PREMI
but I need to see also the other field.
Do I have to set in group by the other field that I need?
thanks
cris
Hi Neelem
I've received an error during the running, this is the code
[PREMI]:
load *
resident PREMI_TMP_2;
inner join
load
ID_TES_PREMI,
max(ID_COD_MIS_PREMI) as Max_Cod_Mis
resident PREMI_TMP_2;
drop table PREMI_TMP_2;
and this is the message
What's I wrong?
Thanks
cristian
You have to have a Group By clause.
so the end would would be
Load
ID_TES_PREMI,
max(ID_COD_MIS_PREMI) as Max_Cod_Mis
Resident PREMI_TMP_2
Group By ID_TES_PREMI;
Yes, With in the Group By Clause you have to declare the all non aggregate fields as well.