topic Re: Max date in QlikView

Max date

Thu, 19 Dec 2013 09:57:45 GMT

Hello,

I'm looking for a straightforward way of loading the following table (but adding a field to show the maximum date of validFrom and validTo)

Roles and Rates:

LOAD amount as [$(vTable) amount],

guid as PK_RATE,

    owner as [$(vTable) owner],

    role as [$(vTable) role],

    rate as [$(vTable) rate],

    facility as [$(vTable) facility],

    Date(floor(validFrom),'DD/MM/YYYY') as [$(vTable) validFrom],

    Date(floor(validTo),'DD/MM/YYYY') as [$(vTable) validTo]

    ;
SQL SELECT *

FROM dbo."ct_rolesAndRates";

Any ideas, please? I have tried experimenting with max(date(validFrom)) etc etc

Thanks.

Re: Max date

jagan — Thu, 19 Dec 2013 10:01:22 GMT

Hi,

Try RangeMax() function in Qlikview

RangeMax(StartDate, EndDate)

Regards,

Jagan.

Re: Max date

Thu, 19 Dec 2013 10:22:23 GMT

Thanks Jagan but I should have explained further.

I am looking for a flag that will show the max date per owner

It currently shows multiple dates per owner and I would like a field that shows the max date per owner

So owner A has validFrom dates – 01/10/12, 01/04/13 and 19/12/2013

Owner B has valid From dates – 01/11/13 and 10/12/13

And so on…for thousands of owners

I would like a flag that will show the max date per each of these records so owner A would show only the record that contains 19/12/13 and owner B 10/12/13 etc etc

Re: Max date

maxgro — Thu, 19 Dec 2013 13:09:22 GMT

If I understand,

you can resident read your table order by owner and validFrom desc (or asc, depending on if you want to flag max or min value) and set a flag when owner is different from previous owner, something (you must adapt to your...) as

T1:

NoConcatenate

load

owner,

.......

validFrom,

if(owner<>peek(owner) or IsNull(owner), 1, 0) as FlagMaxValidFrom

resident <your table>

order by owner, validFrom desc

;

Re: Max date

Anonymous — Thu, 19 Dec 2013 13:15:41 GMT

Add this after your script:

LEFT JOIN (RolesandRates) LOAD DISTINCT
[$(vTable) owner],
max([$(vTable) validFrom]) as OwnerMaxFrom,
max([$(vTable) validTo]) as OwnerMaxTo
RESIDENT RolesandRates
GROUP BY [$(vTable) owner];

Re: Max date

jagan — Fri, 20 Dec 2013 02:34:20 GMT

Hi,

Try like this

Roles and Rates:

LOAD amount as [$(vTable) amount],
guid as PK_RATE,
    owner as [$(vTable) owner],
    role as [$(vTable) role],
    rate as [$(vTable) rate],
    facility as [$(vTable) facility],
    Date(floor(validFrom),'DD/MM/YYYY') as [$(vTable) validFrom],
    Date(floor(validTo),'DD/MM/YYYY') as [$(vTable) validTo];
SQL SELECT *
FROM dbo."ct_rolesAndRates";

LEFT JOIN ([Roles and Rates])

LOAD

[$(vTable) owner],

Max([$(vTable) validFrom]) AS [$(vTable) validFrom]

1 AS Flag

RESIDENT Roles and Rates;

Hope this helps you.

Regards,

Jagan.