Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

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

Tags (1)
11 Replies

Re: Intervalmatch- two event in one interval

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

Re: Intervalmatch- two event in one interval

where to use this in load script ?

Re: Intervalmatch- two event in one interval

No, your expression in the front end GUI.

Not applicable

Re: Intervalmatch- two event in one interval

using this in dimension but giving error

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

Re: Intervalmatch- two event in one interval

No, I meant in your expression, not dimension.

Not applicable

Re: Intervalmatch- two event in one interval

but I require status in dimension only

Not applicable

Re: Intervalmatch- two event in one interval

No, not here

Not applicable

Re: Intervalmatch- two event in one interval


attaching sample.

Re: Intervalmatch- two event in one interval

See attached.

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

Community Browser