how to pick last fiscal year max calmonth rate at backend

[kunkumnaveen]

Hello All,

i got a three yr  data  for FY-17,18,19

                                            FiscalYr  Calmonth      Rates

                                              2018    201712            10

                                              2018    201801            50

                                              2018    201802            100

                                              2018    201803            10.5

                                              2019    201804            200.7

                                              2019    201805           10.7   

                                              2019    201806            60.7

My requirement is for current fiscal year i need to pick last fiscal year march rate (Last fiscal  year max calmonth)

,so in above case i for FY19 i need to pick calmonth=201803 rate which is 10.5.

Can i do that at script level instead of writing it in a set analysis by adding a column

[sunny_talwar]

Where do you want to store this information? in a variable or table? Basically, where and how will this 10.5 going to be used?

[kunkumnaveen · Accepted solution]

I was thinking  to create a new  derived column  of Rates ,But any how i was able to achieve that.

T2:

load * Inline

[

FiscalYear,Calmonth,Rates

2017,201703,100

2018,    201712 ,           10

2018 ,   201801  ,          50

2018  ,  201802 ,           100

2018   , 201803  ,          10.5

2019    ,201804   ,         200.7

2019    ,201805   ,         10.7  

2019    ,201806    ,        60.7

];

join

Load

FiscalYear+1 as FiscalYear,

Rates  as new_Rates

resident T2 where WildMatch(Calmonth,'*03');

load *,

if(len(trim(new_Rates))<=0,0,new_Rates) as new_Rates1

resident T2;

drop table T2;

Output: