Bottom 5 excluding Null values for max year.

[mominh_uzaifa]

Hi Experts,

Need help creating Bottom 5, Based on Max Year excluding null values. As in the data below, I want bottom 5 from Location 10 to Location 14.

Store	2021-2022	2020-2021	2019-2020
Location1	16%	12%	16%
Location2	16%	15%	19%
Location3	16%	12%	18%
Location4	16%	15%	20%
Location5	16%	13%	20%
Location6	15%	15%	21%
Location7	15%	15%	19%
Location8	15%	14%	18%
Location9	14%	13%	16%
Location10	14%	12%	16%
Location11	14%	15%	17%
Location12	13%	15%	19%
Location13	12%	15%	19%
Location14	-630%	14%	18%
Location15	-	-	-
Location16	-	-	23%
Location17	-	11%	11%
Location18	-	-	-
Location19	-	-	18%
Location20	-	-	100%
Location21	-	-	-

Output should be appear like below 

Store	2021-2022	2020-2021	2019-2020
Location10	14%	12%	16%
Location11	14%	15%	17%
Location12	13%	15%	19%
Location13	12%	15%	19%
Location14	-630%	14%	18%

Regards

Huzaifa

[justISO]

Hi, let's call your first table with full data set 't1'. To get output table you can try to use this approach:

//loading rows without null values, sorting from lowest down and taking only 5 top rows
NoConcatenate
main_temp:
LOAD *
RESIDENT t1
WHERE not isnull([2021-2022]) and not isnull([2020-2021]) and not isnull([2019-2020]) 
  and len([2021-2022])>0 and len([2021-2022])>0 and len([2021-2022])>0 
  and rowno()<5
ORDER BY [2021-2022] asc, Store desc
;

//back to normal sorting
NoConcatenate
main:
LOAD *
RESIDENT main_temp
ORDER BY Store asc;

DROP TABLES t1, main_temp;