Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hi Adam,
Do you need to do it in load script or in UI?
reg,
D
I need to see only the relevant entries in UI
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
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;