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.

[sunny_talwar]

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?

[satishkurra]

Yes Sunny, Even i didn't understand that

See if the attached file can help u...

This is similar to what Sunny mentioned

[Gysbert_Wassenaar]

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;

[Anonymous]

Thanks Sunny

I would prefer to have it as a dimension.

Regards

[Anonymous]

Thank you Satish for your input

[Anonymous]

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

Regards.

[Anonymous]

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

[sunny_talwar]

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.

[Anonymous]

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