topic Re: Max function not working in QlikView

Max function not working

Mon, 25 Jul 2016 13:28:36 GMT

Hello all,

I am working with two max functions that aren't working, for the purposes of this project I am using the max to filter duplicates since I only want the latest record. I have tried to manipulate the function a number of different ways and still no luck.

**The max function worked before I added the extra fields called [RC Version Date], [RC Project Name],[RC Project Number], however when a design change was brought forward I added the fields but the max functions seemed to have stopped working since my table came back with 1995 records instead of 1992, so there were 3 duplicates.

LoadJoin:

LOAD *

FROM [\\ke_root.qvd] (qvd);

Left Join

LOAD *

FROM [\\ke_task.qvd] (qvd);

Left Join (LoadJoin)

LOAD [KE Task ID],

[RC Version Date],

[RC Project Name],

[RC Project Number],

Max(Date([Last KE Task Work Info Date Q5])) as [Last KE Task Work Info Date Q5],

Max(Date([Last KE Task Work Info Mod Date Q5])) as [Last KE Task Work Info Mod Date Q5]

FROM

[\\ke_task_work_info.qvd] (qvd)

Group By [KE Task ID], [RC Version Date], [RC Project Name], [RC Project Number];

Thanks,

Nick

Re: Max function not working

MindaugasBacius — Mon, 25 Jul 2016 13:52:40 GMT

Have you tried loading only the last part of your code?

LOAD [KE Task ID],
  [RC Version Date],
  [RC Project Name],
  [RC Project Number],
  Max(Date([Last KE Task Work Info Date Q5])) as [Last KE Task Work Info Date Q5],
  Max(Date([Last KE Task Work Info Mod Date Q5])) as [Last KE Task Work Info Mod Date Q5]
FROM
[\\ke_task_work_info.qvd] (qvd)
Group By [KE Task ID], [RC Version Date], [RC Project Name], [RC Project Number];

Re: Max function not working

Mon, 25 Jul 2016 13:55:55 GMT

Hello Mindauags,

Will not run without the remainder of code.

Re: Max function not working

sunny_talwar — Mon, 25 Jul 2016 13:56:53 GMT

Did you ever try to use the ApplyMap() method of doing this?

Re: Max function not working

Mon, 25 Jul 2016 13:57:30 GMT

I have, no success, sunny...

Re: Max function not working

tom2qlik — Thu, 08 Sep 2016 08:05:50 GMT

Try loading the "RC" fields separately to the max(Date) fields in different tables and then join the two tables together. Something like:

RCLoad:

LOAD

[KE Task ID],

[RC Version Date],

[RC Project Name],

[RC Project Number]

FROM [\\ke_task_work_info.qvd] (qvd);

Inner Join (RCLOAD)

LOAD

[KE Task ID],

Max(Date([Last KE Task Work Info Date Q5])) as [Last KE Task Work Info Date Q5],

Max(Date([Last KE Task Work Info Mod Date Q5])) as [Last KE Task Work Info Mod Date Q5]

FROM

[\\ke_task_work_info.qvd] (qvd);

Left Join (LoadJoin)

Load *

Resident RCLoad;