How to count records by priority items

[#1]

I am creating a bar chart to display how manycustomers have installed the latest software. I have a problem "counting" customers who have the latest software installed on their system. The bar chart displays how many customers are in Win 2002, how many in Win 2003, etc. In the table below there are two columns - "Customer" and "Version". The list contains customer names and the all the software versions that was ever installed on their system from the time they purchased the system.

This is an example of Version and priority from lowest to highest is: Win 2002, Win 2003, Win 2007, Win Vista, Win 7

-------------------------------

Customer     Version

-------------------------------

Cust A          Win 2002

Cust A          Win 2003

Cust A          Win 2007

Cust A          Win 7          --------> Count Cust A for Win 7

Cust B          Win 2002

Cust B          Win 2007
Cust B          Win Vista

Cust B          Win 7          -------> Count Cust B for Win 7

Cust C          Win 2002

Cust C          Vista           --------> Count Cust C for Vista

Cust D          Win 2002

Cust D          Win 2003     -------> Count Cust D for Win 2003

Cust E          Win 2002

Cust E          Win 2003     -------> Count Cust E for Win 2003

The bar must display:

Dimension or X-Axis -> Version

Expressions or Y-Axis -> Count([Customer])

The final data must display only 5 count as there are only 5 customers

1. Win 2002 - 0  (There are no customers with Win 2002)

2. Win 2003 - 2  (Only Cust D and Cust E has Win 2003 as the latest version on therir system)

3. Vista       - 1  (Only Cust C)

4. Win 7 ->  - 2     (Only two customers - Cust A and Cust B have Win 7)

Can someone please suggest how to prioritize the Version and count customer with the latest Version?

Thanks,

[Peter_Cammaert]

Create a mapping table that translates Windows versions to priority. The newer, the lower the priority. Something like:

MapWindowsVersions:

MAPPING LOAD * INLINE [

Version, Prio

Win 7, 0

Vista, 1

Win 2003, 2

Win 2002, 3

XP, 4

];

Then, when loading your source table from the database, add a new column by using applymap() to get the corresponding priority. The default priority for a Windows version that isn't recognized is 5.

:

applymap('MapWindowsVersions', Version, 5) AS Priority,

:

Now, create a table with a single latest-windows-version row per customer.

WindowsVersionCurrentlyInUse:

NOCONCATENATE

LOAD Customer,

     FirstSortedValue(Version, Priority) AS VersionCurrentlyInUse

RESIDENT WindowsInstallationsOrWhateverTheTableIsCalled

GROUP BY Customer;

Now count!

NumberOfUsers:

NOCONCATENATE

LOAD Version,

     Count(Customer) AS NrOfUsers

RESIDENT WindowsVersionCurrentlyInUse

GROUP BY Version;

You may have to tweak this to make it fit your situation.

Best,

Peter

[Peter_Cammaert]

BTW If there is an InstallationDate field in the original Customer-Version table, you can forget about the mapping table, and change the FirstSortedValue() call into this:

:

FirstSortedValue(Version, -num(InstallationDate)) AS VersionCurrentlyInUse,

:

[maxgro · Accepted solution]

I just add a flag to the original table; the flag is used in the chart with set analysis

dim           = Version

expression =(count({$<flag={1}>} Customer))

RESULT

SCRIPT

M:

MAPPING LOAD * INLINE [

from, to

Win 2002, 1

Win 2003, 2

Win 2007, 3

Vista, 4

Win Vista, 4

Win 7, 5

];

C:

load *, applymap('M', Version) as sort;

load Customer, if(Version='Win Vista', 'Vista', Version) as Version

inline [

Customer  ,   Version

Cust A    ,      Win 2002

Cust A    ,      Win 2003

Cust A    ,      Win 2007

Cust A    ,      Win 7       

Cust B    ,      Win 2002

Cust B    ,      Win 2007

Cust B    ,      Win Vista

Cust B    ,      Win 7      

Cust C    ,      Win 2002

Cust C    ,      Vista        

Cust D    ,      Win 2002

Cust D    ,      Win 2003  

Cust E    ,      Win 2002

Cust E    ,      Win 2003

];

C1:

load

*, if(Peek(Customer) <> Customer, 1) as flag

Resident C

order by Customer, sort desc;

drop Table C;

[Peter_Cammaert]

If the OP has an InstallationDate in his source table (he should have), then it gets quite simple:

TaggedWindowsInstallations:

LOAD Customer,

     IF (Version='Vista', 'Win Vista', Version) AS Version,

     IF (peek(Customer) <> Customer, 1, 0) AS Flag

FROM WhatChamaCallit

ORDER BY Customer, InstallationDate DESC;

Good thinking, Massimo. You're a better night owl than I am.

P.

[#6]

Hi Peter,

Thanks for your response. You provided several good options. I do have the installation date. But some customers prefer to change their default operating system from their latest installation version to older version. Because they want their system to be compatible with other older interface devices. That means there are customers who have latest installation date but older version.

[#7]

Hi Massimo,

Thanks for your response. Your solution worked. I had tried several other options. I have not programmed in a long time. I was using FirstSortedValue and Max expressions.

Thanks,

Raghu

[Peter_Cammaert]

So the trick with the installation date will always pick up the latest/actual Windows version, whether it was through an upgrade or a rollback to an older version for certain reasons.

I'm only mentioning this example because it makes your code extremely simple. No more mapping tables, no artificial priority/sort field, no successive LOADs. You should try it.

Peter