Variable string in Set Analysis

[#1]

Hi

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

[tresesco]

Check this : Escape sequences

[#3]

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'

];

[tresesco]

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?

[#5]

yes - i want to change:

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

to

=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.

[tresesco]

Try like:

And then the expression like:

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

Note: No '=' sign in variable definition

[Kushal_Chawda · Accepted solution]

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

[#8]

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.

[#9]

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])

[#10]

Wait - I needed to change the expression!

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

Added '='  - all good - cheers dude!