topic Re: Import expression with nested variable in QlikView

Import expression with nested variable

Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I am trying to import an expression through an external text file which has some variables in the expression. However it is not able to replace the variable value inside the expression. Details below:

Variable to be imported

SET vCurrentVolumeTRx

= sum({<[Time_Vlookup.Month End Date]={">=(=$(vTrendStart))<=($(=vTrendEnd))"},[Time Bucket Type]={'Current'},[CAL_SRC]={'Sales'}>} if([Time_Vlookup.Month End Date]=[Month End Date],TRX_NORM));

Definition of variables used in the above expression

SET vTrendStart	=	min({<[Time_Vlookup.Time Bucket Name]=P([Time Bucket Name]), [Time_Vlookup.Time Bucket Type] = P([Time Bucket Type])>}[Time_Vlookup.Month End Date]) ;
SET vTrendEnd	=	max({<[Time_Vlookup.Time Bucket Name]=P([Time Bucket Name]), [Time_Vlookup.Time Bucket Type] = P([Time Bucket Type])>}[Time_Vlookup.Month End Date]) ;

I am getting the expression after import.

sum({<[Time_Vlookup.Month End Date]={">=<="},[Time Bucket Type]={'Current'},[CAL_SRC]={'Sales'}>} if([Time_Vlookup.Month End Date]=[Month End Date],TRX_NORM))

Appreciate your help.

Regards,

Sam

Re: Import expression with nested variable

vishsaggi — Wed, 21 Sep 2016 16:12:12 GMT

can you try this?

SET variablename = '=sum({<[Time_Vlookup.Month End Date]={"$(= '>=' & $(vTrendStart)) & '<=' $(vTrendEnd))"} ......';

Re: Import expression with nested variable

Thu, 22 Sep 2016 05:43:01 GMT

Thanks Vish for the reply, however using ' in the export statement does not work. The expression value stores all the statement in the load script which have been coded after its declaration.

Re: Import expression with nested variable

vishsaggi — Thu, 22 Sep 2016 16:13:11 GMT

Visibility: @Sunny T

Is it possible to share a sample app ? I am not sure where else to look into, I am sure it is a small modification some where which can resolve. Let us see if Sunny can give some suggestions ?

Re: Import expression with nested variable

Tue, 27 Sep 2016 15:06:40 GMT

I'm actually experiencing much of the same issue. While trying to import the following from an excel file:

Sum({$<MonthYearList = {'$(=Max(MonthYearList))'}, [Trending] = {'Revenue'}>} [Amount]); I lose the entire $(=Max(MonthYearList)) expression within the variable. I have yet to find a solution to this issue.

Re: Import expression with nested variable

vishsaggi — Tue, 27 Sep 2016 16:36:18 GMT

Sunny can you help on this ? stalwar1‌

Re: Import expression with nested variable

Peter_Cammaert — Tue, 27 Sep 2016 16:43:57 GMT

There are a lot of solutions to this $-sign-expands-too-early problem in QlikView Script.

But probably the simplest one is to replace the $-sign in your expression source with another character (one that won't be used for anythjing else, like a pound sign #) and use the Replace() function once the string has been loaded into a variable. Use the Replace() function during the very last step, to avoid getting substituted again.

If you load expressions as strings in your script, and they shouldn't expand yet, use a LET statement and the concatenation operator like in:

LET vMyExpression = 'Sum($' & '(vWhatMustBeReplacedLaterOn))';

Spmitting the $() construct will avoid the $-sign substitution from happening right there and then.

Best,

Peter

Re: Import expression with nested variable

Anil_Babu_Samineni — Sat, 01 Oct 2016 07:33:54 GMT

Have you resolve the problem or else Try this without Variables

sum({<[Time_Vlookup.Month End Date]={'>=' &

"$(min({<[Time_Vlookup.Time Bucket Name]=P([Time Bucket Name]), [Time_Vlookup.Time Bucket Type] = P([Time Bucket Type])>} [Time_Vlookup.Month End Date]))" '<=' &

"$(max({<[Time_Vlookup.Time Bucket Name]=P([Time Bucket Name]), [Time_Vlookup.Time Bucket Type] = P([Time Bucket Type])>}[Time_Vlookup.Month End Date]))"}, [Time Bucket Type]={'Current'}, CAL_SRC={'Sales'}>} if([Time_Vlookup.Month End Date]=[Month End Date],TRX_NORM))

Note: Would you try using UAT for Min and Max Variable on your Text Object. Is there any chance to share Application using Scramble Data.