Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a table with the field Startdate, the table also includes fields for prices and priceid which have diffrent startdates.
For example it looks like this:
Startdate Price PriceID
2018-01-01 55 123456
2018-05-01 60 123456
Im trying to use IterNo to make the result like this:
Startdate Price PriceID
2018-01-01 55 123456
2018-02-01 55 123456
2018-03-01 55 123456
2018-04-01 55 123456
2018-05-01 60 123456
2018-06-01 60 123456
and so on.
This is what I have done:
LOAD
Date(MonthStart(STARTDATE, IterNo()-1)) as Datum
From
While MonthStart(STARTDATE, IterNo()-1)<=MonthStart(STARTDATE)
What I think needs to be done is to change the last STARTDATE in the While statement but cant figure it out.
Also need to use ConvertToLocalTime on STARTDATE but dont know how to when using Date(Monthstart(..
Something like this:
// Load the data from the source Source: LOAD STARTDATE, Price, PriceID FROM ... // Create tables of dates (months) Data: LOAD AddMonths(MinDate, IterNo() - 1) as Month Where AddMonths(MinDate, IterNo() - 1) <= MaxDate; LOAD Min(STARTDATE) as MinDate, Max(STARTDATE) as MaxDate Resident Source; // Interval match the source into the date table (backwards) Join(Data) IntervalMatch(Month) LOAD STARTDATE as From, Alt(Previous(STARTDATE) - 0.1, MakeDate(2999)) as To Resident Source ORDER By STARTDATE DESC; // Optional: bring the price information into the Data table Join(Data) LOAD STARTDATE as From, Alt(Previous(STARTDATE) - 0.1, MakeDate(2999)) Price, PriceID Resident Source ORDER By STARTDATE DESC; // Clean the unneeded elements (if the optional second join is used) DROP Fields From, To; DROP Table Source;
That didnt change anything, the result I get is the same as before..