Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Hi,
Try RangeMax() function in Qlikview
RangeMax(StartDate, EndDate)
Regards,
Jagan.
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
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
;
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];
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.