topic Re: Nested Set Expression in QlikView

Nested Set Expression

Tue, 30 Oct 2012 09:13:32 GMT

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 — Tue, 30 Oct 2012 09:26:42 GMT

Try:

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

Product_Name=p(Product_Name),

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

>} Volume)

Tue, 30 Oct 2012 09:39:14 GMT

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 ??

Tue, 30 Oct 2012 10:19:19 GMT

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.

Tue, 30 Oct 2012 11:37:29 GMT

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

Thanks a ton