Date & Year

[risabhroy_03]

I have a table that has the following columns

Year, Month, Weight

where Year is stored like 2020,2021,2022 and month is stored in numbers like 1,2,3,4.

I have to pick weight for previous month data.

So as on today my weight should be picked for 2,2022.

Please help

[QFabian]

Hi @risabhroy_03 , please check if this works for you.

Aux:
Load * INLINE [
Year, Month, Weight
2022, 3, 76
2022, 2, 75
2022, 1, 74
2021, 12, 73
2021, 11, 72
2021, 10, 71
];

Data:
Load
Year,
Month,
monthname(makedate(Year, Month)) as MonthYear,
if(rowno()=1, Weight, previous(Weight)) as PreviousWeight,
Weight
Resident Aux
order by
Year desc,
Month desc;

drop table Aux;

Output:

[Iswarya_]

try like below:

Data:
Load * INLINE [
Year, Month, Weight
2022, 3, 40
2022, 2, 50
2022, 1, 55
2021, 12, 60
2021, 11, 62
2021, 10, 75
2020, 8, 50
2020, 4, 35
];

Final:
Load
Year,
Month,
Weight,
if(Weight, previous(Weight), null()) as PreviousWeight
Resident Data
order by
Year, Month ,Weight desc;
drop table Data;