topic Set analysis Vs intervalMatch in QlikView

Set analysis Vs intervalMatch

Fri, 15 Apr 2011 12:29:36 GMT

Hi all,

Let's say we have these datas :



// Date Format is : YYYYMMDD
VALUES:
Load * Inline [
ID_PRODUCT, ID_DATE, AMOUNT1, AMOUNT2
501860, 20100117, 50, 1530
501860, 20100215, 65, 1750
];

//Rates where START_DATE is the starting date of the rate. So, the next DATDEB would be the end date...
//For example, in the first line, "20090101" is the "start date" of the rate "4", and "20091130" is the "end date" of this same rate...
RATE:
Load * Inline [
ID_PRODUCT, START_DATE, RATE
501860, 20090101, 4
501860, 20091130, 5
501860, 20091230, 5.1
501860, 20100115, 5.3
501860, 20100130, 6
501860, 20100217, 7
];

Once I have this reloaded, both tables are joined with the field ID_PRODUCT.

My problem is that I want to have a graph table which has to look like that :

ID_PRODUCT, ID_DATE, AMOUNT1, RATE

501860 20100117 50 5.3

501860 20100215 65 6

The hard part is the last column named (RATE).

I tried this with bad results :

sum( If( START_DATE = (max( if( START_DATE < ID_DATE, START_DATE) )), RATE))

sum( {<START_DATE = {"=max( {<START_DATE={"<ID_DATE"]>} START_DATE)"}>} RATE)

In english, what I want is the "RATE" of the last "START_DATE" which is before "ID_DATE"...

Please, don't tell me to do an "IntervalMatch" since I don't want to do that in the script : I have a 1 billion table to Match and it takes like 1 hour...

Thanks a lot for your replies.

Jonrod

Set analysis Vs intervalMatch

Fri, 15 Apr 2011 13:32:25 GMT

maybe you should see this. If this is fit in your case.

http://community.qlik.com/forums/p/23510/89770.aspx#89770

Set analysis Vs intervalMatch

Miguel_Angel_Baeyens — Fri, 15 Apr 2011 14:12:12 GMT

Hello Jonrod,

Try the following expression, is that what you are looking for?

FirstSortedValue(RATE, -IF(START_DATE < ID_DATE, START_DATE))

I didn't do an intervalmatch with such a volume of data unless strictly necessary.

Anyway, since the aggregation function has a conditional, it may take long to display the chart, so now that you know how does it perform doing an intervalmatch, I'd try the expression before.

Hope that helps.

Set analysis Vs intervalMatch

Oleg_Troyansky — Fri, 15 Apr 2011 15:22:34 GMT

FirstSortedValue() is the right approach, however it might not be such a good idea to use it in run-time on a 1 Bil. rows table - unless your chart is ALWAYS running over a smaller subset of the data, defined by narrow selections.

You may want to use a similar approach in your load script. By the way, if you need this kind of advanced analysis performed on a 1 bil. rows, a 1 hour addition to the load time is not such a bad penalty - consider your alternatives... It's better to add 1 hour to the data load, than to cause a 40 seconds lag in the run-time, in front of a user...

Set analysis Vs intervalMatch

Fri, 15 Apr 2011 16:37:58 GMT

Oh Yes!! That's It!!

Thank you very much for your replies.

I've been working on it for hours... Need to learn a lot of you folks!

The FirstSortedValue() worked for me and it don't "lag" because my graph is filtered on a few lines (less than 100 000...).

Thanks

Jonrod