Current Year Vs Prior Year Script

[NemoAndStitch22]

Hiya, 

I'm looking to add in the script a bit to categorise as prior year vs current year. 

So all my files are labelled the same With the differences being the Month and Year. 

What I would like to be able to do is have Qliksense identify that the latest file in date is May 2019, and that is categorised as Current Year, and then have it find the one that's May 2018 and have that categorised as Prior Year so I can compare the two in multiple tables. 

I can't seem to find something a previous thread that works/gives me what I need, but I have got Qlik to recognise the May 2019 etc as a date and this is my script to do that.

Please could someone help? 

Thanks

[Vegar]

You could solve it like this:

map:
mapping LOAD * 
inline [
  A1, B1
  0, Current Year
  -1, Prior Year
];

Data:
LOAD 
  Period , 
  Year 
INLINE [
  Period, Year
  Mar, 2019
  Apr, 2019
  Feb, 2018
  Jul, 2017
];
JOIN LOAD 
  MAX(Year) as LatestYear 
RESIDENT 
  Data;

LEFT JOIN LOAD
  applymap('map',Year - LatestYear, null()) as RelYear,
  *
RESIDENT Data;
DROP FIELD LatestYear;

[NemoAndStitch22]

Hi Vegar,

Thanks for this, but I'm not sure it will work in the long run, There will be data for every month going back to 2016 and I don't want to have to edit the script every month when this is run, especially as I might not be the one running it each time... 

Is there another way?

Thanks,

Megan

[Vegar]

Oh, I think I misslead you, you don't have to do monthly adjustments with my solution.

You should replace the part below with your own data, the script reading file names. I just used an inline[]  as simple way to show you the concept. 

Data:
LOAD 
  Period , 
  Year 
INLINE [
  Period, Year
  Mar, 2019
  Apr, 2019
  Feb, 2018
  Jul, 2017
];

[NemoAndStitch22]

Ah okay, gotcha thank you, when I do this, and adapt to my data it doesn't like the Year field...

Every time I have year written it's the same.. 

Do you know why this would be? 

[Vegar]

It looks like you haven't managed to create a year field at the point you
are referring to it. I have no better guess without looking at your
script/data model.

-Vegar