Seperating a data set

[Study]

I have a data set that looks like this, and I would like to create a chart that shows the average of all scores that contain Each location. 

Steinbach ;Winnipeg;Home Office (MB/SK/AB/BC);	25%
Steinbach ;	17%
Steinbach ;	22%
Steinbach ;	20%
Home Office (MB/SK/AB/BC);	25%
Steinbach ;Winnipeg;Home Office (MB/SK/AB/BC);	33%
Steinbach ;Home Office (MB/SK/AB/BC);	33%
Steinbach ;Winnipeg;	25%
Steinbach ;	11%
Steinbach ;	21%
Steinbach ;	14%
Winnipeg;Home Office (MB/SK/AB/BC);	9%

For example 

Winnipeg - (Average of scores that contain Winnipeg)

Steinbach (Average of scores that contain Steinbach)

ETc. 

[QFabian · Accepted solution]

Hi @Study ,  please check if this works for you.

its about the SUBFIELD function

https://help.qlik.com/en-US/cloud-services/Subsystems/Hub/Content/Sense_Hub/Scripting/StringFunction...

Aux:
LOAD
"Job Location",
"Overall Hazard Score",
"Ergonomic Hazards"
FROM [lib://Datos (fabian_fabian)/Test Case.xlsx]
(ooxml, embedded labels, table is Sheet1);

Load
"Job Location",
SubField(left("Job Location", len("Job Location")-1), ';') as "Job Location 2",
"Overall Hazard Score"

Resident Aux;
drop table Aux;

[RafaelBarrios]

Hi @Study 

I would recommend that you first split those records that could have different values by using subfield

https://help.qlik.com/en-US/qlikview/May2022/Subsystems/Client/Content/QV_QlikView/Scripting/StringF...

load 
…

subfield(field1,';') as any_name

…

and then your chart will be easy

dimension: any_name

measure: avg(field2)

Otherwise you will need to use multiple expressions for each possible dimension value 

expression: avg({< field1={"*Steinbach*"} >} field2)

hope this helps.

best,

[luiferva]

Hola Buenas tardes.

No se si te logre comprender bien, lo que entendi es que necesitas separar donde diga Steinbach y Winnipeg, despues mostrar el promedio de los marcadores.

use el siguiente script

T:
LOAD 
     [Job Location], 
     [Overall Hazard Score], 
     [Ergonomic Hazards],
     if(SubStringCount([Job Location],'Steinbach')>0,[Overall Hazard Score]),0 as Steinbach,
     if(SubStringCount([Job Location],'Winnipeg')>0,[Overall Hazard Score]),0 as Winnipeg
FROM
[..\Fuentes externas\Test Case.xlsx]
(ooxml, embedded labels, table is Sheet1);

y en la presentación me queda de esta forma

Por favor comenta si te resulto útil.

Feliz Dia

[Study]

Thank you very much for this. works perfectly.