Load INLINE problem

[conwayro]

Hi all,

I'm having an issue grouping data into 'buckets' via Load INLINE.

In my database I have three separate location fields depending on the facility (Location A, Location B and Location C).  In my load script I concatenate these into a fourth field simply called "Location" so that I can display all locations in a single column in a Table Box.  The script for this is:

     [Location(A)] as Location_A,
     [Location(B)] as Location_B,
     [Location(C)] as Location_C,
     
//===This next line combines all three locations into one location column=== //  
     [Location(A)]&''&[Location(B)]&''&[Location(C)] as Location,

The result is:

I now want to create a 5th field called 'Service Group' which assigns the appropriate Service to the location to look something like this:

I tried to do this using the following script:

LOAD * INLINE [
    Location, ServiceGroup
        
    OPD, 'Clinical Access & Operations'
    ACC Clinic 2, 'Clinical Access & Operations'
    ACC Clinic 3, 'Clinical Access & Operations'
    ACC Clinic 4, 'Clinical Access & Operations'
    ACC Clinic 5, 'Clinical Access & Operations'
    ACC Clinic 6, 'Clinical Access & Operations'
    ACC Clinic 7, 'Clinical Access & Operations'
    OUTPATIENTS, 'Clinical Access & Operations'
    Transit Lounge, 'Clinical Access & Operations'
    
    2FW-ARC,'Medical Services'
    3A,'Medical Services'

etc......

But when I use the 'Location' field, it will only do it for a small amount of the data for the previous year.  If I replace 'Location' with one of the three individual fields, such as 'Location_A' the data loads accurately, but only for those locations in the 'Location_A' field, and not for the other two!

Any suggestions would be greatly received!

Cheers

Roger

[punitpopli]

Please share a sample workbook which will help us to get you a right solution

on the other note, your Service Group field is behaving on the data association which is correct.

you can try considering other two fields as will in custom Service Group field using set analysis to include those two fields 

e.g. : only({<LocationB,LocationC>}service group)

Please note this might not be exactly what you want and might need to tweak.

[Gysbert_Wassenaar · Accepted solution]

Looking at your screenshot it looks like you have values in Location_A and Location_B that are single spaces. So if you string these values together with Location_C you get values with one or two preceding spaces. You need to remove those spaces with for example the Trim() function: Trim([Location(A)] & [Location(B)] & [Location(C)]) as Location

[conwayro]

Thank you!  This solved the issue!