How to sum based on dates

[#1]

Hello,

I have the need to sum a value based on the closest matching date to another.  I have a score that is pulled at a date and tied to a customer but not directly tied to a transaction.  The date the score is pulled is stored and the date the transaction executed is stored.  Since the score is not tied to the transaction and to the customer my data is not showing properly.  So I would like to put some set analysis in so that I only grab the score from the closest date prior to the execution date.

From the dummy data below I would only want to see the first row, I have tried a few things but no luck.  Any help would be much appreciated.

Trans ID	Score	ScoreDate	Execution Date	Amount
1234	450	3/31/2015	4/15/2015	200
1234	560	1/20/2015	4/15/2015	200

[sunny_talwar]

May be this:

Table:

LOAD [Trans ID],

    Score,

    ScoreDate,

    [Execution Date],

    Amount,

    [Execution Date] - ScoreDate as Range

FROM

[https://community.qlik.com/thread/217587]

(html, codepage is 1252, embedded labels, table is @1);

Left Join (Table)

LOAD [Trans ID],

  Min(Range) as Range,

  If(Min(Range) > 0, 1) as Flag

Resident Table

Group by [Trans ID];

[#3]

That is close but if the value for the Transaction is less than 0 I would want to take the lowest positive value and this doesn't mark any flags for that transaction. 

[sunny_talwar]

When you say value, do you mean Score or Amount?

[#5]

I meant the Range.  I actually was able to complete it.

Left Join (Table)

LOAD [Trans ID],

  Min(Range) as Range,

  1 as Flag

Resident Table

Where Range >0

Group by [Trans ID];

[sunny_talwar]

Great

[#7]

Thank you very much for your help.

[sunny_talwar]

No problem at all. I am glad I was able to help