topic Re: How to load only max values in QlikView

How to load only max values

dimiqlik — Thu, 13 Feb 2020 10:11:33 GMT

In my script I only want to load the max values from "tblprestatieblok.levelNr" per PlanningID

below my script

LOAD tblprestatieblok.PlanningRef                                                              as PlanningID,
     tblprestatieblok.GepresteerdeUren1                                                        as     GepresteerdeUren,
     Upper(ApplyMap ('prmuurcode' , tblprestatieblok.CodeUren1 , '<Fout>'     ))   as   CodeUren,
     Upper(ApplyMap ('UurcodeCHQP_SD' , tblprestatieblok.CodeUren1 , '<Fout>' ))   as     LooncodeSD,
     tblprestatieblok.LevelNr
FROM
$(vQVDPath)tblprestatieblok.qvd

Re: How to load only max values

Taoufiq_Zarra — Thu, 13 Feb 2020 10:21:33 GMT

Data: LOAD tblprestatieblok.PlanningRef as PlanningID, tblprestatieblok.GepresteerdeUren1 as GepresteerdeUren, Upper(ApplyMap ('prmuurcode' , tblprestatieblok.CodeUren1 , '<Fout>' )) as CodeUren, Upper(ApplyMap ('UurcodeCHQP_SD' , tblprestatieblok.CodeUren1 , '<Fout>' )) as LooncodeSD, tblprestatieblok.LevelNr FROM $(vQVDPath)tblprestatieblok.qvd output: noconcatenate load PlanningID, max(tblprestatieblok.LevelNr) as MaxValue resident Data group by PlanningID; drop table Data:

Re: How to load only max values

tresB — Thu, 13 Feb 2020 10:23:42 GMT

May be with a JOIN, like:

LOAD tblprestatieblok.PlanningRef as PlanningID,
tblprestatieblok.GepresteerdeUren1 as GepresteerdeUren,
Upper(ApplyMap ('prmuurcode' , tblprestatieblok.CodeUren1 , '<Fout>' )) as CodeUren,
Upper(ApplyMap ('UurcodeCHQP_SD' , tblprestatieblok.CodeUren1 , '<Fout>' )) as LooncodeSD
tblprestatieblok.LevelNr
FROM
$(vQVDPath)tblprestatieblok.qvd;

JOIN tblprestatieblok.PlanningRef as PlanningID, Max(tblprestatieblok.LevelNr) as Max_tblprestatieblok.LevelNr FROM $(vQVDPath)tblprestatieblok.qvd Group By tblprestatieblok.PlanningRef;

Re: How to load only max values

dimiqlik — Thu, 13 Feb 2020 11:22:33 GMT

Hi Taoufiq

Doesn't seem to do the trick I don't get any output. Script runs however

Re: How to load only max values

dimiqlik — Thu, 13 Feb 2020 11:52:20 GMT

Hi Tresesco

I tried Join but it doens't give me the desired output it still gives all the values

Re: How to load only max values

Taoufiq_Zarra — Thu, 13 Feb 2020 11:54:20 GMT

can you share a sample data and the output ?

Re: How to load only max values

sunny_talwar — Thu, 13 Feb 2020 11:58:19 GMT

Are you looking to load only those rows where the value is max per PlanningID? May be this

Table: LOAD tblprestatieblok.PlanningRef as PlanningID, tblprestatieblok.GepresteerdeUren1 as GepresteerdeUren, Upper(ApplyMap('prmuurcode', tblprestatieblok.CodeUren1, '<Fout>')) as CodeUren, Upper(ApplyMap('UurcodeCHQP_SD', tblprestatieblok.CodeUren1, '<Fout>')) as LooncodeSD, tblprestatieblok.LevelNr FROM $(vQVDPath)tblprestatieblok.qvd; Inner Join (Table) LOAD PlanningID, Max(tblprestatieblok.LevelNr) as tblprestatieblok.LevelNr Resident Table Group By PlanningID;

Re: How to load only max values

dimiqlik — Thu, 13 Feb 2020 13:07:14 GMT

Hi Sunny

Thanks! This does the trick

Re: How to load only max values

oleggggggM — Mon, 03 Jan 2022 21:36:49 GMT

Hello @sunny_talwar

solution works great in case I keep just two values in the resulting table (PlanningID and tblprestatieblok.LevelNr) - thank you for your valuable advice.

Would you have any advice in case I need to keep more values from the original table, e.g.

PlanningID,

tblprestatieblok.LevelNr,

GepresteerdeUren,

LooncodeSD

So to keep all columnds from the original table, but kepping only those rows where the value is max per PlanningID. Thank you for your help in advance.