Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
I have a table with companies and their ratings (Moody's ratings) and have already assigned a ranking value for each of the rating. How do I compare two periods of the ratings and provide a Status column that says "Upgrade", "Downgrade", or "No Change"?
See below table (ratings are no base on real life ratings!):
Month | Company | Moody | Rating Rank |
Oct | Apple | A1 | 5 |
Oct | IBM | Aa1 | 2 |
Oct | Genetech | Aa3 | 4 |
Oct | Samsung | Baa1 | 8 |
Sept | Apple | Aa3 | 4 |
Sept | IBM | Aa2 | 3 |
Sept | Genetech | Aaa | 1 |
Sept | Samsung | Baa1 | 8 |
and I wish to see something like this:
Company | Current Rating | Current Rating Rank | Prior Rating | Prior Rating Rank | Status |
Apple | A1 | 5 | Aa3 | 4 | Downgrade |
IBM | Aa1 | 2 | Aa2 | 3 | Upgrade |
Genetech | Aa3 | 4 | Aaa | 1 | Downgrade |
Samsung | Baa1 | 8 | Baa1 | 8 | No Change |
thanks
Louise
like this:
tabRatingRank:
LOAD Dual([Moody’s], RecNo()) as Moody
FROM [http://de.wikipedia.org/wiki/Moody%E2%80%99s](html, codepage is 1252, embedded labels, table is @2, filters(Remove(Row, Pos(Top, 2)),Remove(Row, Pos(Top, 25)),Remove(Row, Pos(Top, 24))));
mapRatingRank:
Mapping LOAD Text(Moody), Moody
Resident tabRatingRank;
tabCompanyRating:
LOAD Month(Date#(Left(Month,3),'MMM')) as Month, //Left(,3) because of typo in posted sample data
Company,
ApplyMap('mapRatingRank', Moody) as Moody
FROM [http://community.qlik.com/thread/141152] (html, codepage is 1252, embedded labels, table is @3);
hope this helps
regards
Marco
See attached qvw
... or in the script something like this
TempData :
load
Month,
[Month Num] ,
Company,
Moody,
[Rating Rank],
previous ( [Rating Rank] ) as [Prior Rating Rank]
Resident Temp
order by Company , [Month Num]
;
drop table Temp ;
left join ( TempData)
load
*
;
load
Company ,
max ( [Month Num] ) as MaxMonth
resident TempData
group by Company
order by Company
;
Data :
NoConcatenate
load
*
resident TempData
where MaxMonth = [Month Num]
;
drop table TempData ;
drop table Temp ;
left join ( TempData)
load
*
;
load
Company ,
max ( [Month Num] ) as MaxMonth
resident TempData
group by Company
order by Company
;
Data :
NoConcatenate
load
*
resident TempData
where MaxMonth = [Month Num]
;
drop table TempData ;
I forgot to add in one more criteria, the "Current Month" will be selected by the user...so I dont want to hard code the month in the expression. thanks.
Hi Bill,
I am not very good at reading script, so I cant say that I undersatnd your solution. Is it possible to ask you to make it into a qvw file. also, like I have responded to Gysbert's posting, my user will be making the selection for the current month. so here is a sample table with more months.
Month | Company | Moody | Rating Rank |
Nov | Apple | A1 | 5 |
Nov | IBM | A2 | 6 |
Nov | Genetech | A3 | 7 |
Nov | Samsung | Baa3 | 10 |
Oct | Apple | A1 | 5 |
Oct | IBM | Aa1 | 2 |
Oct | Genetech | Aa3 | 4 |
Oct | Samsung | Baa1 | 8 |
Sept | Apple | Aa3 | 4 |
Sept | IBM | Aa2 | 3 |
Sept | Genetech | Aaa | 1 |
Sept | Samsung | Baa1 | 8 |
Aug | Apple | Aaa | 1 |
Aug | IBM | Baa2 | 9 |
Aug | Genetech | Ba1 | 11 |
Aug | Samsung | Caa1 | 17 |
thanks a lot
Louise
Hi,
one solution could be:
Using a dual load of the rating with an underlying ranking value, i.e. sorting of the rating field will be correct without any additional sorting expression.
tabRatingRank:
LOAD Dual(Moody, MoodyRank) as Moody;
LOAD [Moody’s] as Moody,
RecNo() as MoodyRank
FROM [http://de.wikipedia.org/wiki/Moody%E2%80%99s](html, codepage is 1252, embedded labels, table is @2, filters(Remove(Row, Pos(Top, 2)),Remove(Row, Pos(Top, 25)),Remove(Row, Pos(Top, 24))));
mapRatingRank:
Mapping LOAD Text(Moody), Moody
Resident tabRatingRank;
tabCompanyRating:
LOAD Month(Date#(Left(Month,3),'MMM')) as Month,
Company,
ApplyMap('mapRatingRank', Moody) as Moody
FROM [http://community.qlik.com/thread/141152] (html, codepage is 1252, embedded labels, table is @3);
Edit: tabRatingRank could be implemented without preceding load.
hope this helps
regards
Marco
like this:
tabRatingRank:
LOAD Dual([Moody’s], RecNo()) as Moody
FROM [http://de.wikipedia.org/wiki/Moody%E2%80%99s](html, codepage is 1252, embedded labels, table is @2, filters(Remove(Row, Pos(Top, 2)),Remove(Row, Pos(Top, 25)),Remove(Row, Pos(Top, 24))));
mapRatingRank:
Mapping LOAD Text(Moody), Moody
Resident tabRatingRank;
tabCompanyRating:
LOAD Month(Date#(Left(Month,3),'MMM')) as Month, //Left(,3) because of typo in posted sample data
Company,
ApplyMap('mapRatingRank', Moody) as Moody
FROM [http://community.qlik.com/thread/141152] (html, codepage is 1252, embedded labels, table is @3);
hope this helps
regards
Marco
Hi Marco, your solution looks like it should work out for me. I havent have the time to test it out yet (sorry for the delay). I will definitely give it a try next week and let you know! thanks
Louise