Nested Set Expression

[#1]

I have written a set expression

sum({1<Market_Name={'A'}, Product_Name=p(Product_Name), Channel_Name=p(Channel_Name)>} Volume)

I have another expression which gives me possible channel value for the product type

concat({$<Product_Type={'A'}>} Distinct Channel_Name)

I want to insert this expression in the above expression in place of channel name. So basically my

Channel_Name= concat({$<Product_Type={'A'}>} Distinct Channel_Name)

So how do i write nested set expression to do this.

Regards,

Sumit Harsh

[Gysbert_Wassenaar · Accepted solution]

Try:

sum({1< Market_Name={'A'},

                Product_Name=p(Product_Name),

                Channel_Name={ $(=concat({$<Product_Type={'A'}>} Distinct Channel_Name,',')) }

            >} Volume)

[#3]

Thanks Gysbert but this is working when my possible channel is only 1, if it is more than 1 i am getting 0 value.

I guess we need to concatenate channel names. But i am not sure how to use concat again in the set expression ??

[#4]

heres a copy & paste of somehting which may help when setting variables ect.

• $(varName)   - returns the expansion of the variable (performed before the statement is executed). This is the same as typing the contents of the variable. If the contents are an expression, it will be evaluated when the statement is executed. You can use this form when the variable is defined (for example):
  • Set v1 = 2; (2, evaluated, returns 2)
  • Set v2 = Count(Distinct ClientID); injects this expression fragment which is evaluated when the statement or expression is executed.

• '$(varName)'  - also return the expansion, but treats the contents as a string. In other word, when the statement is executed, the variable's content will be placed in the string and not evaluated. For example, if variable v1 contains 2012/10/26, then
  • $(v1) will result in 2012 divided by 10, divided by 26 (7.7385)
  • '$(v1)' will result in the string '2012/10/26'

• varName - returns the value of the variable (like a conventional variable in other programming languages). You can use this form when the variable is defined as (for example):
  • Set v1 = 2;
  • Set v2 = =Count(Distinct ClientID); (returns the pre-evaluated expression. Note the 2 = symbols)
  • you cannot use this form in a LOAD or SELECT statement - it will be interpreted as a field name.

• 'varName' - will simply return a string containing 'varName', and makes no reference to the variable at all.

[#5]

Thanks Gysbert the last expression was only working for multiple channels, i was missing that comma after channel name.

Thanks a ton