Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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
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
];
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?