Last non null value per id,year and month

[kprotrka]

I have to get last non null value per,id,year and month. First of all i grouped the price so each id has one price per month and year. 

ID&Year&Month 1202110

Now i need to take last non null value from this field. If it is in March 2021 price 10 and in April 0 I need to show it in April,May,Juni,July,August,September,October,November,December as 10. 

If another ID has the last price in May as 15 then needs to be shown till December as 15(for this specific ID).

[rubenmarin]

Hi, I think for this you will need:

- A calendar with all the dates you want to fill.

- Make an outer join of this calendar with all the different IDs to create a cartesian product with all dates an ids

- Concatenate the values to you data to field the dates gap by ID, you can use Exists() to avoid to add rows that already has data

Concatenate(data)
LOAD ID&Year&Month as chkLoadedData, // this field has to exists also in you original data
  ID,
  Year,
  Month
Resident CartesianTable
Where not Exists('chkLoadedData', ID&Year&Month);

Load from the data that now have all dates by ID and use peek() to fill the Price

LOAD
  ID,
  Year,
  Month,
  If(Peek(ID)=ID and IsNull(Price), Peek(Price), Price) as Price
Resident DataWithFilledDates
Order By ID, Year, Month;

You can also do another load with "Order By ID, Year desc, Month desc" to fill prices backwards.

[kprotrka]

I am not sure how to do it.

Basically I need to calculate the forecast. I have to take the last non zero value from the past for the specific ID.

For example ID is1 and in March 2021 had Betrag_AP 100. In April it is 0.In May also 0. I need to take this 100 from today till the last date(December 2021). So basically for this ID 100 should be shown from the current month till December 2021.

Also if the field Betrag_AP is 0 for the whole time for the specific ID, then it should be shown 0 till December 2021. Thanks a lot 🙂

[rubenmarin]

Hi, this should be done in script, following the steps in my previous post.

From your data you can create all months with a calendar like this:

CartesianTable:
LOAD 
	 AddMonths(MinDate, IterNo()-1) 			as IdDate
While AddMonths(MinDate, IterNo()-1) <= MaxDate
;
LOAD 
	 MonthStart(Min(FieldValue('IdDate', RecNo()))) 	as MinDate,
	 MonthStart(YearEnd(Max(FieldValue('IdDate', RecNo()))))as MaxDate
AutoGenerate FieldValueCount('IdDate');

This creates all the months from the first month in data to december of the max date found.

- The outer join is just:

OuterJoin (CartesianTable)
LOAD Distinct ID resident [YourDatatableName];

Whith this you have all dates related to every ID, then follow the next steps of the previous post to fill the gaps on your data, and once you have a table without gaps (any ID has a record for each month) use the last script to fill the price.

If you found an issue try to ask for the specific issue you found following the steps.

Regards.