RANGEMAX issue - QlikView

[tjagdev5]

Hi All,

I'm having an issue with the RANGEMAX function in my load script.

I have a table with 2 columns - project IDs and a field/column of 'milestones' per project (so multiple milestone dates).

I want to get the 'latest' (so closest to today) date per project - that is what i tried to achieve with line ****-1. However, that is not working as the Latest_Gateway_Date field output returns all the dates, not the latest date as I expect.

I then want to match that latest date back to itself to reference what value is in 'Gateway_Type' - this is line ****-2. E.g. if the date matches the date for Gateway_4 (which is a Gateway_Type value) for example, then it will return me 'Gateway_4'. I think this is falling over because of line ****-1, however.

The code is below - any help would be greatly appreciated

Many thanks

Taz

LOAD DISTINCT Project_ID,

RANGEMAX(Gateway_Date,Project_ID) AS Latest_Gateway_Date ****- 1

IF(Latest_Gateway_Date=Gateway_Date,Gateway_Type) AS Latest_Gateway_Type ****- 2

FROM

[$(vG.QVDPath)$(vL.GatewaysPath)] (qvd)

[vishsaggi]

You cannot refer the Alias name in the same load for IF condition. May be try preceding load like:

From Reference Help:

RangeMax() returns the highest numeric value found within the expression or field.

So you are trying to pick the max date comparing with Project_ID which is not date. Is this right? May be you want to pick Max date from two dates like highlighted below:

LOAD *,

          IF(Latest_Gateway_Date=Gateway_Date,Gateway_Type) AS Latest_Gateway_Type ****- 2;

LOAD DISTINCT Project_ID,

RANGEMAX(Gateway_Date, AnotherDateField) AS Latest_Gateway_Date ****- 1

FROM

[$(vG.QVDPath)$(vL.GatewaysPath)] (qvd):

OR

Get the Max dates like below

LOAD *,

          IF(Latest_Gateway_Date=Gateway_Date, Gateway_Type) AS [Latest_Gateway_Type];

LOAD DISTINCT Project_ID,

          MAX(Gateway_Date) AS [Latest_Gateway_Date]

FROM

[$(vG.QVDPath)$(vL.GatewaysPath)] (qvd)

Group By Project_ID: