Select single record

[#1]

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

[Anil_Babu_Samineni]

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;

[neelamsaroha157]

Noconcatenate

Load *

resident PREMI_TMP_2;

Drop table PREMI_TMP_2;

inner join Load Lotto,

          Max(Misura) as MaxMisura

resident PREMI_TMP_2;

[#4]

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

[#5]

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

[Anonymous · Accepted solution]

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;

[Anil_Babu_Samineni]

Yes, With in the Group By Clause you have to declare the all non aggregate fields as well.