Reference line via script and with values

[Anonymous]

Hello qlikers 🙂 ,

I have created my reference line via formula in the chart, see below please

IF((GetFieldSelections([City]) = 'Berlin') AND (GetFieldSelections([Name]) = 'Michael'), 2.0,
IF((GetFieldSelections([City]) = 'Berlin') AND (GetFieldSelections([Name]) = 'Sophie'), 2.0,
IF((GetFieldSelections([City]) = 'Berlin') AND (GetFieldSelections([Name]) = 'Michelle)'), 4.5,
IF((GetFieldSelections([City]) = 'Berlin') AND (GetFieldSelections([Name]) = 'Carloin'), 0.5,
IF((GetFieldSelections([City]) = 'Berlin') AND (GetFieldSelections([Name]) = 'Mike'), 0.5,
IF((GetFieldSelections([City]) = 'London') AND (GetFieldSelections([Name]) = 'Carloin'), 2.0,
IF((GetFieldSelections([City]) = 'London') AND (GetFieldSelections([Name]) = 'Mike'), 1.0,
IF((GetFieldSelections([City]) = 'Hamburg') AND (GetFieldSelections([Name]) = 'Michael'), 2.0,
IF((GetFieldSelections([City]) = 'Hamburg') AND (GetFieldSelections([Name]) = 'Sophie'), 2.0,
IF((GetFieldSelections([City]) = 'Hamburg') AND (GetFieldSelections([Name]) = 'Carloin'), 2.0,
IF((GetFieldSelections([City]) = 'Hamburg') AND (GetFieldSelections([Name]) = 'Mike'), 1.0,
IF((GetFieldSelections([City]) = 'Hamburg') AND (GetFieldSelections([Name]) = 'Michelle)'), 3.0,
)
)
)
)
)
)
)
)
)
)
)
)

You can see the formula is very short, static and unprodcutiv.

Is there a option to create this in the load script ? I have also an excel file with the values, is there a option to loop over the excel file and create this, for example:

Hallo
Loop over the excel file, Head city = valuex, Head name = valuey, Head values, valuez
IF((GetFieldSelections([City]) = 'valuex') AND (GetFieldSelections([Name]) = 'valuey'), valuez)
Endloop

I hope you know what I mean. Here is a small screenshot from the excel file

How can I do that more effective, dynamic and better ? 🙂 Thank you in advance! 

[y_grynechko]

Good Morning,

here is the way you need to build the loop, I am sure you can figure out the rest 😉 

Temp:
load * inline [
ID,City,Name, Value
1,Berlin,Michael,2
2,Berlin,Sophie,2
3,Berlin,Michelle,4.5
4,Berlin,Carloin,0.5
5,Berlin,Mike,0.5
];

FOR i = 0 to (FieldValueCount('ID')-1)

LET v_City = Peek('City', $(i), 'Temp');
LET v_Name = Peek('Name', $(i), 'Temp');
LET v_Value = Peek('Value', $(i), 'Temp');
Trace IF((GetFieldSelections([City]) = $(v_City)) AND (GetFieldSelections([Name]) = $(v_Name)), $(v_Value),;
NEXT i

[sunny_talwar · Accepted solution]

What if you use this after loading your Excel file?

If(GetSelectedCount(City) = 1 and GetSelectedCount(Name) = 1, Value)

[Anonymous]

Thank you very much! 🙂