How to combine data entries with similar date?

[#1]

I am having data series like the following:

Product	Date of Advertisement	Price on Advertisement
Product A	2013-10-27	$400
Product A	2013-11-05	$380
Product B	2013-05-03	$800
Product B	2013-08-10	$700
Product B	2013-09-04	$900
Product C	2014-02-01	$200

What I need to do is to find out the latest advertisement date as well as price. But if 2 advertisement for the product is within 30 days, we still take the earlier one. So I will get the following in the resultant table:

Product	Date of Advertisement	Price on Advertisement
Product A	2013-10-27	$400
Product B	2013-08-10	$700
Product C	2014-02-01	$200

I tried to use max() and max(,2) function but there could be more than 2 advertisements for the same product that are posted within 30 days. Could anyone help me with this? What kind of functions are suitable in this case?

Million thanks!

Adam

[#2]

Hi Adam,

Do you need to do it in load script or in UI?

reg,

D

[#3]

I need to see only the relevant entries in UI

[eduardo_sommer]

I have an idea but I'm failing with the set analysis syntax. Suppose your fields are Product, DateAd and Price.

Using a straight table, the dimension will be Product

The expressions:

=FirstSortedValue({$<DateAd={">$(=max(DateAd)-30)<=$(=max(DateAd))"}>} DateAd, DateAd)

=FirstSortedValue({$<DateAd={">$(=max(DateAd)-30)<=$(=max(DateAd))"}>} DateAd, Price)

Eduardo

[anbu1984]

Tab:

Load Product,Date(Date#(Date,'YYYY-MM-DD')) As Date,Price Inline [

Product,Date,Price

Product A,2013-10-27,400

Product A,2013-11-05,380

Product B,2013-05-03,800

Product B,2013-08-10,700

Product B,2013-09-04,900

Product C,2014-02-01,200

Product D,2014-02-01,200

Product D,2014-04-01,400

];

NoConcatenate

Tab1:

Load * Where Rank <= 2;

Load Product,Date,If(RowNo() = 1 or Previous(Product) <> Product, 0, If(Previous(Date) - Date <= 30,Previous(Date) - Date,-1)) As Diff,

If(RowNo() = 1 or Previous(Product) <> Product, 1, Peek('Rank') + 1) As Rank,

Price, AutoNumber(Product & Date) Resident Tab

Order By Product,Date desc ;

NoConcatenate

Final:

Load Product,FirstSortedValue(Date,-Diff),FirstSortedValue(Price,-Diff) Resident Tab1 Group by Product;

DROP Table Tab;

DROP Table Tab1;