topic Compare non-numeric data in two periods in QlikView

Compare non-numeric data in two periods

Thu, 06 Nov 2014 18:02:42 GMT

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

Re: Compare non-numeric data in two periods

Gysbert_Wassenaar — Thu, 06 Nov 2014 18:30:19 GMT

See attached qvw

Re: Re: Compare non-numeric data in two periods

Anonymous — Thu, 06 Nov 2014 18:43:01 GMT

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

Re: Compare non-numeric data in two periods

Thu, 06 Nov 2014 18:48:01 GMT

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.

Re: Compare non-numeric data in two periods

Thu, 06 Nov 2014 19:20:31 GMT

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

Re: Compare non-numeric data in two periods

MarcoWedel — Fri, 07 Nov 2014 00:25:58 GMT

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

Re: Re: Compare non-numeric data in two periods

MarcoWedel — Fri, 07 Nov 2014 23:53:55 GMT

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

Re: Compare non-numeric data in two periods

Sat, 08 Nov 2014 01:51:52 GMT

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