Compare non-numeric data in two periods

[#1]

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

[Gysbert_Wassenaar]

See attached qvw

[Anonymous]

... 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 ;

[#4]

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.

[#5]

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

[MarcoWedel]

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

[MarcoWedel · Accepted solution]

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

[#8]

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