Tough expression - any ideas ?

[stockisteven]

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.

Thanks !

[OmarBenSalem]

try:

1) aggr("Service Date" - min("Service Date"),Name)

or

aggr("Service Date" -  aggr(min("Service Date") ,Name),Name)

2) aggr(max("Service Date" )- min("Service Date"),Name)

or

aggr(aggr("Service Date",Name) -  aggr(min("Service Date") ,Name),Name)

[stockisteven]

Thanks Omar.

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?

Thanks !

[OmarBenSalem]

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

[petter]

This should be the way to do it in a load script:

LOAD

  Name,

  Only([Service Date])-Min([Service Date])+1 AS [Day #],

  Max([Service Date])-Min([Service Date])+1 AS [Total Stay]

RESIDENT

  STAYS   // or the name of the table where you have your raw stay data

GROUP BY

Name;

[stockisteven]

Hi Petter,

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 !

[stockisteven]

I can do this in excel with the "MaxIf" or "MinIf".. trying to find something similar in qliksense.

[marcus_sommer]

It's just guessing from your screenshot what do you want to do and I could imagine that you need something like this:

Table:

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.

- Marcus