Place comma separated string value from Excel in a QlikView variable

[jjordaan]

Hello everyone,

I have a question and I think it should be very simple.

I am developing a dashboard based on a Navision database.

Some settings for the dashboard I place in an Excel file which I then load up in the script.

In the Excel file I have 3 columns ID, Name and Value.

The ID = 4, Name = InternalTTCodes, Values ​​= TT115, TT330, TT365, TT400 etc.

In the script I created a variable with a Peek function;

LET vInternalTT = Peek('ExcelSetupValue', 4, 'ExcelSetup');

When I created a textbox with the variable like this = vInternalTT I retrieve the values​​.

Ony When I created a Set Analysis like this;

=Sum({<ReasonCode = {'$(vInternalTT)'}>} VESalesAmountActual)

I retrieve the value 0.

Can some help me with this issue.

Thank you in advance.

[Gysbert_Wassenaar]

Does the ReasonCode field also contain comma separated values? Are you expecting ReasonCode = {'TT115, TT330, TT365, TT400'} or ReasonCode ={'TT115', 'TT330', 'TT365', 'TT400'}? The first is what you're getting. If you need the second you could split the Values field with the subfield function so you get a record for every value in the string and rename it to ReasonCode so it's associated the the other ReasonCode field. You can then use ID and Name in your set analysis expression instead to select the correct ReasonCodes.

[jjordaan]

Gysbert,

In the Navision database the values are seperated per row.

The table in NAV looks like this;

Code

TT115

TT330

TT456

Etc.

I created a seperated Excel sheet where the customer can add specific settings. One of the settings are to indicated what kind off Reason Codes are Internal codes.

The Excel file looks like

ID     Name                    Value

4      InternalTTCodes      TT115, TT330, TT456, Etc.

In the load script I use the Peek function to fill the the variable like this

LET vInternalTT = Peek('ExcelSetupValue', 4, 'ExcelSetup');

In a chart expression I want to use the Value in a SET ANALYSIS like this.

=Sum({<ReasonCode = {'$(vInternalTT)'}>} VESalesAmountActual)

How can I ensure that I can use the external excel setup file where I can fill in the reason codes and retrieve them through a variable.

Thx for your help

[Gysbert_Wassenaar]

Jeroen,

I think I understand your setup now. You need the codes in the Value field in the excel sheet enclosed in single quotes. One way is to make the users put in the single quotes. Another way is to put in the quotes in the script

chr(39) & replace(replace(Value,' ',''),',', chr(39)&','&chr(39)) & chr(39) as Value. This is a more user friendly option.

You also need to remove the quotes in the set expression:

=Sum({<ReasonCode = {$(vInternalTT)}>} VESalesAmountActual)

[jjordaan]

Gysbert,

Thx for your help.

I add a sample Excel and QVW file where I used your suggestion.

Can you check if there is a better way to do like I did?

Thx again for the help

[Gysbert_Wassenaar · Accepted solution]

I would use bookmarks where at all possible. These are very easy for users to work with. When enabled on the server it's also possible to let users create and share bookmarks. Using bookmarks may not always be possible, like where you need set expressions to specifically exclude/include values independent of selections. Using an external file to create variables will work. It's up to you to decide if it's worth the effort or if manually adding the variables and modify the expressions is less work.

With regards your load script. You could use an if statement to leave out the InternalReasonCodes table:

Setup:

LOAD

          ID,

          Setup,

          if(ID=4,Chr(39) & Replace(Replace(Value,' ',''),',',Chr(39) &','&Chr(39)) & Chr(39),Value) as Value

FROM

[Control File .xlsx]

(ooxml, embedded labels, table is Setup);

LET vInternalTT  =      Peek('Value', 4, 'Setup');

[jjordaan]

Gysbert,

Thanks again for your tremendous help!!