Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a straight table containing dimensions,
Year , Week Number , Day name
2016 , 1 , Mon
2016 , 2 , Tue
2016 , 2 , Wed
2015 , 1 ,Mon
2015 , 3 , Tue
I have attached the sample data. I would like to rank those rows group by Year. Like below
Year , Week Number , Day name , Rank
2016 , 1 , Mon , 1
2016 , 2 , Tue , 2
2016 , 2 , Wed , 3
2015 , 1 ,Mon , 1
2015 , 3 , Tue , 2
Ranking should restart with new year. Please help.
Thanks,
Mugdha
Try this out:
Table:
LOAD Year,
week,
Dual(day, Match(day, 'Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat', 'Sun')) as day
FROM
[..\..\..\Downloads\Test Data.xls]
(biff, embedded labels, table is Sheet1$);
FinalTable:
LOAD *,
AutoNumber(RecNo(), Year) as Rank
Resident Table
Order By Year, week, day;
DROP Table Table;
can we do something in the set formula?
I am loading different tables with different years hence in my script if I create rank, It wont work in the chart. Can we rank the chart rows according to their year. I used RowNo(total) and its giving me correct sequence number but it restart at new year,it continues till the last row.
Hi,
I've changed the attached as per the output rank required.
It is working but in my case the logic is bit complicated related to the year hence I can not change my script. I need something in set analysis