topic Re: Select single record in QlikView

Select single record

Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Select single record

Anil_Babu_Samineni — Wed, 19 Jul 2017 15:48:46 GMT

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;

Re: Select single record

neelamsaroha157 — Wed, 19 Jul 2017 16:00:19 GMT

Noconcatenate

Load *

resident PREMI_TMP_2;

Drop table PREMI_TMP_2;

inner join Load Lotto,

Max(Misura) as MaxMisura

resident PREMI_TMP_2;

Re: Select single record

Wed, 19 Jul 2017 16:33:49 GMT

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

Re: Select single record

Wed, 19 Jul 2017 16:43:24 GMT

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

Re: Select single record

Anonymous — Wed, 19 Jul 2017 16:54:26 GMT

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;

Re: Select single record

Anil_Babu_Samineni — Thu, 20 Jul 2017 03:04:45 GMT

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