Premises classification

[Anonymous]

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.

[Anonymous]

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.

[sunny_talwar]

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

[Anonymous]

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