Discussion board where members can get started with Qlik Sense.
Easier to explain using the picture attached.
The 2 far right columns I am trying to accomplish in the data load editor.
I'm hitting a mind block where I just can't find the right expression to use to accomplish the task.
Any help would be appreciated.
1) aggr("Service Date" - min("Service Date"),Name)
aggr("Service Date" - aggr(min("Service Date") ,Name),Name)
2) aggr(max("Service Date" )- min("Service Date"),Name)
aggr(aggr("Service Date",Name) - aggr(min("Service Date") ,Name),Name)
Quick Q - I have to use this in the data load editor on a table that has about 15 other columns.
Meaning, I can only use your expression via a "Group By" clause correct ?
Do you know how that expression could be written in the data load editor without havintg to use the Group by Clause?
well it's being late now.. can u please attach a sample excel file of maybe 10 rows with ur 15 columns? I"ll take a look at this tomorrow; i promise
This should be the way to do it in a load script:
Only([Service Date])-Min([Service Date])+1 AS [Day #],
Max([Service Date])-Min([Service Date])+1 AS [Total Stay]
STAYS // or the name of the table where you have your raw stay data
Thanks but for some reason that didn't work - it just produced a value of 1.
I tired different date formats (floor, datekey, num, etc.) but still nothing.
Any other ideas? Thanks !
It's just guessing from your screenshot what do you want to do and I could imagine that you need something like this:
load Name, [Service Date], autonumber(Name &'|' & [Service Date]) as [Day #]
resident PreviousFromLoad order by Name, [Service Date];
left join (Table)
load Name, max([Service Date]) - min([Service Date]) as [Total Stay]
resident Table group by Name;
whereby it's important to sort (could be only applied by a resident-load) the data properly within the first load.