topic Re: Variable string in Set Analysis in QlikView

Variable string in Set Analysis

Thu, 18 May 2017 10:40:46 GMT

I'm having some trouble using a dynamic string in set analysis.

The following expression works:

=Sum({< UnusedProductGroup={'Non Panel B&C' , 'Non Panel Landlords'}>} [TotalCost])

But I create an inline script so I can make the chart dynamic: (this is cut down so it's clear!)

LOAD * INLINE [

Id, Comparison, Productname1

|10|, Non Panel v Panel, Non Panel, "'Non Panel B&C' , 'Non Panel Landlords'"

|20|, Non Panel Landlord vs Non Panel B&C, Non Panel Landlord

|30|, Panel Landlord vs Panel B&C, Panel Landlord

];

I then change the expression to:

=Sum({< UnusedProductGroup={$(=ProductName1)}>} [TotalCost])

In the inline script, the ProductName1 bit comes in as "'Non Panel B&C' , 'Non Panel Landlords'". Sadly this isnt working. I assume its how its sees the string, perhaps as a search string? How do i get around this? I dont want to use a variable for each field, as later on I may need to have more complex strings with 5 or 6 concatenations! How should I string multiple fields together?

Cheers

Re: Variable string in Set Analysis

tresB — Thu, 18 May 2017 11:08:41 GMT

Check this : Escape sequences

Re: Variable string in Set Analysis

Thu, 18 May 2017 11:27:56 GMT

Hi - Thanks - I read the article and tried to implement, but I can't get it to work - It either ignores, adds in double quotes, which i think then get reviewed as search strings, or other weirdness!

as here:

LOAD * INLINE [

Id, Comparison, Productname1

|10|, Non Panel v Panel, 'Non Panel B&C'', ''Non Panel Landlords'

];

Re: Variable string in Set Analysis

tresB — Thu, 18 May 2017 11:41:45 GMT

I am not really sure what you trying to achieve. Are you just trying to put separate strings together in a variable to be used in set analysis?

Re: Variable string in Set Analysis

Thu, 18 May 2017 11:43:44 GMT

yes - i want to change:

=Sum({< UnusedProductGroup={'Non Panel B&C' , 'Non Panel Landlords'}>} [TotalCost])

=Sum({< UnusedProductGroup={$(=ProductName1)}>} [TotalCost])

Where ProductName1 would be the same as 'Non Panel B&C' , 'Non Panel Landlords'

However - I have a number of this UnusedProductGroup combinations to make dynamic.

Re: Variable string in Set Analysis

tresB — Thu, 18 May 2017 11:52:08 GMT

Try like:

And then the expression like:

=Sum({<UnusedProductGroup={$(ProductName1)}>} [TotalCost])

Note: No '=' sign in variable definition

Re: Variable string in Set Analysis

Kushal_Chawda — Thu, 18 May 2017 11:54:46 GMT

try this

Data:

LOAD *,

replace(Productname1,'|',',') as Productname2;

LOAD * INLINE [

Id, Comparison, Productname1

|10|, '''Non Panel v Panel ''|'' Non Panel''', '''Non Panel B&C''|''Non Panel Landlords'''

];

then try using Productname2 in set analysis

Re: Variable string in Set Analysis

Thu, 18 May 2017 12:08:03 GMT

Thanks Tresesco - this works fine as a standalone variable, but if i want to load it inline, then make the chart dynamic it sees the comma as a new field.

Re: Variable string in Set Analysis

Thu, 18 May 2017 12:12:25 GMT

I can see what this is doing... but it still returns 0 in the chart...

using this -

Data:

LOAD *,

replace(Productname1,'|',',') as Productname2;

LOAD * INLINE [

Id, Comparison, Productname1

|10|, '''Non Panel v Panel ''', '''Non Panel B&C''|''Non Panel Landlords'''

];

and this: =Sum({<UnusedProductGroup={$(Productname2)}>} [TotalCost])

Re: Variable string in Set Analysis

Thu, 18 May 2017 12:14:21 GMT

Wait - I needed to change the expression!

=Sum({<UnusedProductGroup={$(=Productname2)}>} [TotalCost])

Added '=' - all good - cheers dude!