Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
conwayro
Contributor
Contributor

Load INLINE problem

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:

Capture.JPG

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

Capture.JPG

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

1 Solution

Accepted Solutions
Gysbert_Wassenaar

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


talk is cheap, supply exceeds demand

View solution in original post

3 Replies
punitpopli
Specialist
Specialist

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

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


talk is cheap, supply exceeds demand
conwayro
Contributor
Contributor
Author

Thank you!  This solved the issue!