Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I would like to show a simple price history in a table and diagram.
For this I have a date and a part number as the dimension and the price. The date is linked to the master calendar.
In the raw data there are part numbers with several dates with the same price and different prices (see table).
Example:
PartNo | Date | Price |
11000 | 01.01.2021 | 15,50 |
11000 | 03.01.2021 | 15,50 |
11000 | 04.01.2021 | 16,00 |
11000 | 06.02.2021 | 16,10 |
11000 | 07.02.2021 | 16,10 |
11000 | 09.04.2021 | 16,10 |
11000 | 10.04.2021 | 13,00 |
In the price history, only one price change should be used.
If the price remains the same at another point in time, it should not be displayed.
The result should then look like this:
PartNo | Date | Price |
11000 | 01.01.2021 | 15,50 |
11000 | 04.01.2021 | 16,00 |
11000 | 06.02.2021 | 16,10 |
11000 | 10.04.2021 | 13,00 |
Does anyone have any idea how I can do this?
@reporting_neu try below
Data:
LOAD PartNo,
Date,
Price
FROM Table.qvd;
Final:
LOAD *
where Price_Flag=1;
LOAD *,
if((PartNo=Previous(PartNo) and Date<>Previous(Date) and Price<>Previous(Price))
or PartNo<>Previous(PartNo),1,0) as Price_Flag
Resident Data
Order by PartNo,Date;
DROP Table Data;
DROP Field Price_Flag;
Or can you help me with a Script in the editor?
I've tried SQL before. Unfortunately not with the desired result.
SELECT
part
, min(datefield) AS datefield
, price
FROM Database
GROUP BY part, datefield, price
ORDER BY part, datefield
@reporting_neu try below
Data:
LOAD PartNo,
Date,
Price
FROM Table.qvd;
Final:
LOAD *
where Price_Flag=1;
LOAD *,
if((PartNo=Previous(PartNo) and Date<>Previous(Date) and Price<>Previous(Price))
or PartNo<>Previous(PartNo),1,0) as Price_Flag
Resident Data
Order by PartNo,Date;
DROP Table Data;
DROP Field Price_Flag;
many thanks for your response.
I understood the logic behind the formula. But unfortunately the formula does not bring the desired result.
This is what the result should look like:
The formula actually only picks up the differentiated data. But somehow it doesn't do that either.
@reporting_neu If you are following the exact script which is provided it should work. Can you share your script?
I'm so sorry!
It works!!! 😀
I accidentally used the same names in the columns, which is why synthetic keys were used and the other data was also displayed. Now I have a perfect result!
Do you have any ideas how I can list the first value? He only checks the change in the formula. I would always need this after the first value. If you had that, everything would be perfect! 😅
My mistake. That works too.
I've just ruled out part of the formula.
//or Part<>Previous(Part)
Re-inserted, it works great!
Can you help me one more time? I would like to add customers as an additional dimension. The price history should therefore be displayed for each customer and part.
Like:
Customer | PartNo | Date | Price |
1 | 11000 | 01.01.2021 | 15,50 |
1 | 11000 | 04.01.2021 | 16,00 |
1 | 11000 | 06.02.2021 | 16,10 |
1 | 11000 | 10.04.2021 | 13,00 |
2 | 11000 | 02.01.2021 | 15,60 |
2 | 11000 | 04.01.2021 | 15,90 |
2 | 11000 | 05.02.2021 | 16,10 |
2 | 11000 | 10.04.2021 | 13,00 |
If I add the following it doesn't work, sorry.
if((Customer=Previous(Customer) and PartNo=Previous(PartNo) and Date<>Previous(Date) and Price<>Previous(Price))
or PartNo<>Previous(PartNo) or Customer<>Previous(Customer),1,0) as Price_Flag
@reporting_neu try below
if((Customer=Previous(Customer) and PartNo=Previous(PartNo) and Date<>Previous(Date) and Price<>Previous(Price))
or (PartNo<>Previous(PartNo) or Customer<>Previous(Customer)),1,0) as Price_Flag