topic Re: How to count records by priority items in QlikView

How to count records by priority items

Tue, 04 Mar 2014 21:56:37 GMT

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,

Re: Re: How to count records by priority items

Peter_Cammaert — Tue, 04 Mar 2014 23:28:33 GMT

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

Re: Re: Re: How to count records by priority items

Peter_Cammaert — Tue, 04 Mar 2014 23:31:55 GMT

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,

Re: How to count records by priority items

maxgro — Tue, 04 Mar 2014 23:53:01 GMT

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

MAPPING LOAD * INLINE [

from, to

Win 2002, 1

Win 2003, 2

Win 2007, 3

Vista, 4

Win Vista, 4

Win 7, 5

];

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;

Re: Re: How to count records by priority items

Peter_Cammaert — Wed, 05 Mar 2014 00:09:45 GMT

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.

Re: How to count records by priority items

Wed, 05 Mar 2014 20:49:32 GMT

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.

Re: How to count records by priority items

Wed, 05 Mar 2014 20:51:38 GMT

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

Re: How to count records by priority items

Peter_Cammaert — Thu, 06 Mar 2014 13:38:54 GMT

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