Set analysis Vs intervalMatch

[#1]

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))

or

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

[#2]

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

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

[Miguel_Angel_Baeyens · Accepted solution]

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.

[Oleg_Troyansky]

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...

[#5]

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