Only show the newest date (valid from)

[PescheSwitzerland]

Hi there 🙂

I have a list of products with list prices and different valid-from dates to this prices. Now I simply want to show only the newest valid-from dates (one per product). Thats it 😄 

Coud you please help me out with the script function. Attached you find the table load.

Thanks a lot!
Pesche

[steeefan]

Let's assume the table of which you posted the LOAD script is called Product:

ProductMaxDate:
NOCONCATENATE LOAD
  product_id,
  MAX([Price valid from]) AS MaxValidFrom
RESIDENT
  Product
GROUP BY
  product_id;

You will now have a second table, ProductMaxDate, containing each value for product_id and the corresponding highest [Price valid from] value.

[PrashantSangle]

Hi,

max() or firstsortedvalue() will help you

try below

test:

yourscript;

Left join

Load product_id

date(max([Price valid from]) as "Latest Price Valid From"

Resident test

Group by product_id;

Regards,

Prashant Sangle

[PescheSwitzerland]

Thanks again @steeefan - Now it shows up now the newest date in each row, but all [List Price]'s - just with the new date - what could we change that it shows up only one row with the product and the newest price? 

Thanks!

[steeefan]

If that's what you want, you can JOIN the max. date to your table and create a flag:

LEFT JOIN (Product) LOAD
  product_id,
  MAX([Price valid from]) AS [Price valid from],
  True() AS isMaxDate
RESIDENT
  Product
GROUP BY
  product_id;

You can then filter on isMaxDate, either in the script in a WHERE clause or on your dashboard.

ProductF:
NOCONCATENATE LOAD
  *
RESIDENT
  Product
WHERE
  isMaxDate;

DROP TABLE Product;

[PescheSwitzerland]

Ok, then it shows up 29.12.1899 in each row 😅 what can I change to fix this? 

Thanks!

[steeefan]

That's just the date representation of the value -1, which is the value of the newly introduced flag isMaxDate.

[PescheSwitzerland]

Ah ok, I got it, of course. And how can I show the date itself? 🙂 Thanks for all your help! 😊

[steeefan]

You simply need to show the values of [Price valid from] where isMaxDate=-1, either by filtering in the script with a WHERE clause or on your dashboard (Set Analysis, selections).