topic Premises classification in QlikView

Premises classification

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

I have the following information:

I need to be able to classify the 'Premises Name' based on the count of the premises name. For example, the circled premises name is Greenwood Pharmacy and has a count of two or more pharmacies and would be classified as a 'Chain' otherwise if the count is less than two it would be classified as an 'Independent'.

Can this be accomplished at load time or in the front end of the application?

Regards.

Re: Premises classification

sunny_talwar — Wed, 30 Mar 2016 16:08:22 GMT

It can be done either of the places. You just need to check if(Count(Premises Name)>= 2, ...

Where are you planning to show this info? dimension or expression?

Re: Premises classification

satishkurra — Wed, 30 Mar 2016 16:14:38 GMT

Yes Sunny, Even i didn't understand that

See if the attached file can help u...

This is similar to what Sunny mentioned

Re: Premises classification

Gysbert_Wassenaar — Wed, 30 Mar 2016 16:21:21 GMT

It's not all that easy since the names are not the same. You probably want GREENWOOD PHARMACY and GREENWOOD PHARMACY - ...something... to be counted as 1 pharmacy chain. You may have to use a mapping table to map different names that mean the same pharmacy to a new value that will be the same for all the pharmacies that should be grouped as one.

mapPharmacy:

MAPPING

LOAD * INLINE [

PharmacyName, PharmacyChain

GREENWOOD PHARMACY, GREENWOOD PHARMACY

GREENWOOD PHARMACY (GREYHURST) - , GREENWOOD PHARMACY

...etc

Foobar Pharmacy,Foobar Pharmacy

Foobar Pharmacy Blue, Foobar Pharmacy

Foobar Pharmacy Pink, Foobar Pharmacy

...etc

];

TempPharmacies:

LOAD

PharmacyName,

[Premises Location'],

...other fields...

Applymap('mapPharmacy',PharmacyName) as PharmacyChainName

FROM

....

;

JOIN (Pharmacies)

LOAD

PharmacyChainName

Count(PharmacyName) as CountOfPharmacies

RESIDENT

TempPharmacies

;

Pharmacies:

LOAD

If(CountOfPharmacies>1, 1, 0) as IsChain

RESIDENT

TempPharmacies

;

DROP TABLE TempPharmacies;

Re: Premises classification

Anonymous — Wed, 30 Mar 2016 21:41:58 GMT

Thanks Sunny

I would prefer to have it as a dimension.

Regards

Re: Premises classification

Anonymous — Wed, 30 Mar 2016 21:42:19 GMT

Thank you Satish for your input

Re: Premises classification

Anonymous — Wed, 30 Mar 2016 21:43:17 GMT

Thanks Gysber that sounds great. Let me try implementing it and will revert.

Regards.

Re: Premises classification

Anonymous — Thu, 31 Mar 2016 00:36:18 GMT

Hi Sunny

Tried the following but there is something wrong with my expressions:

I am using ApplyMap to clean the Premises Name so that it is uniform.

Regards

Re: Premises classification

sunny_talwar — Thu, 31 Mar 2016 00:43:47 GMT

I don't see a group by statement when you do a count. I think you will have to do a join when you do a count instead of doing it in the preceding load.

Re: Premises classification

Anonymous — Thu, 31 Mar 2016 09:43:33 GMT

Dear Gysbert

I tried implementing your recommendations but i am not succeeding.

The Join(Pharmacies) stage, where is this table coming from as i get an error message of an invalid table name.

Regards

Re: Premises classification

Anonymous — Thu, 31 Mar 2016 09:45:55 GMT

I have tried to do the count within the main load statement, my problem is i have an ApplyMap function to clean the 'Premises Name' first before use. I am failing to comprehend how i do the count within the main load as it tells me the field 'Premises Name' not found and that is why i was using a preceding load.

Regards.

Re: Premises classification

sunny_talwar — Thu, 31 Mar 2016 09:55:29 GMT

Can you copy paste the above code here (instead of an image)

Re: Premises classification

Anonymous — Thu, 31 Mar 2016 14:36:41 GMT

Sunny, please find herewith pasted code:

PremisesMappingTable:
Mapping LOAD PremisesType,
PremisesDescription
FROM
[..\Data Files\MCAZ\PremisesMappingTable.xlsx]
(ooxml, embedded labels, table is Sheet1);

RetailPharmacyMapping:
Mapping LOAD OldPharmacyName,
CleanPharmacyName
FROM
[..\Data Files\MCAZ\RetailPharmacyMapping.xlsx]
(ooxml, embedded labels, table is Sheet1);

Premises:
LOAD *,
If(CountOfPharmacies>2,'Chain', 'Independent') as RetailClass;

LOAD *,
Count([Premises Name]) as CountOfPharmacies
Group by pexpiry,"plic_num",plocation,plocation2,pname, pphone,pstatus,
psuperv1, psuperv2,ptown,ptype;

LOAD Date(pexpiry) as [Premises License Expiry],
"plic_num" as [Premises Licence Number],
plocation as [Premises Location],
plocation2 as [Premises Location2],
ApplyMap('RetailPharmacyMapping',pname) as [Premises Name],
Text(pphone) as [Premises Phone],
pstatus as [Premises Status],
psuperv1 as [Premises Supervisor],
psuperv2 as [Premises Supervisor2],
ptown as [Premises Town],
ApplyMap('PremisesMappingTable', ptype) as [Premises Type];
SQL SELECT pexpiry,
"plic_num",
plocation,
plocation2,
pname,
pphone,
pstatus,
psuperv1,
psuperv2
ptown,
ptype
FROM PharmaMarketAudit.dbo.tblMCAZLicensedPremises;

Regards