Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Live chat with experts, bring your API Integration questions. June 15th, 10 AM ET. REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
dimiqlik
Contributor
Contributor

How to load only max values

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

 

1 Solution

Accepted Solutions
sunny_talwar

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;

View solution in original post

7 Replies
Taoufiq_Zarra
Master II
Master II

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:
Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
tresesco
MVP
MVP

 

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
Contributor
Contributor
Author

Hi Taoufiq

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

dimiqlik
Contributor
Contributor
Author

Hi Tresesco

 

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

Taoufiq_Zarra
Master II
Master II

can you share a sample data and the output ?

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
sunny_talwar

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;

View solution in original post

dimiqlik
Contributor
Contributor
Author

Hi Sunny

 

Thanks! This does the trick