Load max date and hour

[sanjujeeboy]

Hi All,

I have a table like this

Country	Date	Hour	Sales
germany	11/7/2019	1	100
germany	11/7/2019	2	101
germany	11/7/2019	3	102
germany	11/7/2019	4	103
Italy	11/7/2019	1	103
Italy	11/7/2019	2	104
Italy	11/8/2019	3	105
Spain	11/7/2019	1	106
Spain	11/7/2019	2	107
Spain	11/8/2019	3	108

Requirement is i want to get max date max hour sales for each country. So the output should be:

Country	Date	Hour	Sales
germany	11/7/2019	4	103
Italy	11/8/2019	3	105
Spain	11/8/2019	3	108

How to write a set expression for this?

[waszcma1]

in script mode write this:

Load 
Country,

max(Date) as Date,

max(hour) as hour,

max(Sales) as Sales

from your_source_file.qvd(qvd) group by Country;

[Arthur_Fong]

If you want to create in expression, try this:

Hour > aggr(max(Hour),Country)

Sales > aggr(max(Sales),Country)

[sanjujeeboy]

i have loaded in script mode.

how to write expressions in table to achieve output?

[tresesco · Accepted solution]

Hi Sanjujeeboy,

I can get the desired output with the solution provided here in your other post here: https://community.qlik.com/t5/New-to-QlikView/Load-max-hour/m-p/1643796#M383043

Are you still looking for Set Analysis solution, or something is missing ? If you continue with one thread, it would be better for others and you as well - to reach the solution you need.

[sanjujeeboy]

Hi Tresesco

I have put the same expression but its not working in qliksense.

although this expression works in qlikview but not working in qliksense.

can you try in qliksense and let me know.

Thanks in advance.

[tresesco]

It should also work in qlik sense. Try to share your sample qvf that is not working...and let me have a look at it and fix the issue for you.

[sanjujeeboy]

Hi Tresesco,

Its working in qliksense also. there was some data model issues, thats why data was not showing. But now its fixed.

Thanks a lot for your help.