Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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,
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;
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
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,
:
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;
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.
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.
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
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