How to load only max values

[dimiqlik]

Hi

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

[Taoufiq_Zarra]

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:

[tresesco]

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;

[dimiqlik]

Hi Taoufiq

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

[dimiqlik]

Hi Tresesco

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

[Taoufiq_Zarra]

can you share a sample data and the output ?

[sunny_talwar · Accepted solution]

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;

[dimiqlik]

Hi Sunny

Thanks! This does the trick

[oleggggggM]

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.