topic Re: Load max date and hour in QlikView

Load max date and hour

sanjujeeboy — Thu, 07 Nov 2019 15:49:24 GMT

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?

Re: Load max date and hour

waszcma1 — Thu, 07 Nov 2019 22:21:09 GMT

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;

Re: Load max date and hour

Arthur_Fong — Fri, 08 Nov 2019 03:39:18 GMT

If you want to create in expression, try this:

Hour > aggr(max(Hour),Country)

Sales > aggr(max(Sales),Country)

Re: Load max date and hour

sanjujeeboy — Fri, 08 Nov 2019 06:33:01 GMT

i have loaded in script mode.

how to write expressions in table to achieve output?

Re: Load max date and hour

tresB — Fri, 08 Nov 2019 06:49:57 GMT

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.

Re: Load max date and hour

sanjujeeboy — Fri, 08 Nov 2019 10:04:18 GMT

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.

Re: Load max date and hour

tresB — Fri, 08 Nov 2019 10:13:02 GMT

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.

Re: Load max date and hour

sanjujeeboy — Fri, 08 Nov 2019 10:33:37 GMT

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.