Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Intervalmatch- two event in one interval

Hi,

I have table with from_date1,to_date1 and from_status. I have created one calendar for all dates.

Now I used IntervalMatch for creating table which will give status and calendar date.

from_status, to_status, from_date,to_date

Open,            Closed,    01/01/2016,01/01/2016

After Interval Match

calendar date, from_status,from_date,to_date

01/01/2016,    Open,           01/01/2016,01/01/2016

01/01/2016,    Closed,         01/01/2016,01/01/2016

Required:

calendar date, from_status,from_date,to_date

01/01/2016,    Closed,         01/01/2016,01/01/2016

for one date, Status got changed from Open to Closed so for this event, It is showing two entries in table which is correct but I want to show only second status (Closed) for that date

11 Replies
Anonymous
Not applicable
Author

The FirstSortedValue() frunction against Status will return the value for only Closed as that is alphabetically first.

Here is the Help entry for it :

firstsortedvalue([{set_expression}][ distinct ] [ total [<fld {, fld}>]] expression [, sort_weight [, n]])

returns the first value of expression sorted by corresponding sort-weight when expression is iterated over the chart dimension(s). Sort-weight should return a numeric value where the lowest value will render the corresponding value of expression to be sorted first. By preceding the sort-value expression with a minus sign, the function will return the last value instead. If more than one value of expression share the same lowest sort-order, the function will return null. By stating an n larger than 1, you will get the nth value in order.

Examples:

firstsortedvalue ( PurchasedArticle, OrderDate )     

firstsortedvalue ( PurchasedArticle, -OrderDate, 2 )     

firstsortedvalue ( A/B, X*Y/3 )    

firstsortedvalue ( distinct PurchasedArticle, OrderDate )     

firstsortedvalue ( total PurchasedArticle, OrderDate )    

firstsortedvalue ( total <Grp> PurchasedArticle, OrderDate ) 

Not applicable
Author

where to use this in load script ?

Anonymous
Not applicable
Author

No, your expression in the front end GUI.

Not applicable
Author

using this in dimension but giving error

firstsortedvalue(From_status,-from_date) as I want last status for date.

Anonymous
Not applicable
Author

No, I meant in your expression, not dimension.

Not applicable
Author

but I require status in dimension only

Not applicable
Author

No, not here

Not applicable
Author


attaching sample.

Anonymous
Not applicable
Author

See attached.

Is the object at the top left of Sheet1 what you are after ?