topic Re: Source Control, Variables and Set Analysis in QlikView

Source Control, Variables and Set Analysis

gksmithlcw — Wed, 10 Sep 2014 21:35:54 GMT

Hello All,

I'm currently trying to get a document into SVN via QlikView's integrated SVN support and through my research, I found that variables entered into the Variables tab of the Document Properties Dialog will not persist in source control so my first step was to move all of my variables to my script (best practice anyway) and reload to make sure everything came through as expected.

Things looked great until I got to a variable in which set analysis and dollar sign expansion were used. Here's what I I'm dealing with:

The variable value from the dialog as it was before moving it to the script:
=Sum({<EnrollmentDate_SD1 = {">=$(#vPlanYear_CurrentStart)"}, ClmPdDt_MonthYear=, IncurFromDt_MonthYear=, EnrollmentStatus={'Employee'} >}[Medically Eligible Month])
The script I wrote to replace what had been entered into the dialog previously:
SET vPlanYear_Current_Employee_Months = "=Sum({<EnrollmentDate_SD1 = {">=$(#vPlanYear_CurrentStart)"}, ClmPdDt_MonthYear=, IncurFromDt_MonthYear=, EnrollmentStatus={'Employee'}>}[Medically Eligible Month])";
The variable value from the dialog after the reload:
"=Sum({<EnrollmentDate_SD1 = {">=0"}, ClmPdDt_MonthYear=, IncurFromDt_MonthYear=, EnrollmentStatus={'Employee'}>}[Medically Eligible Month])"

So it looks to me like instead of pulling my variable value through like it did with other variables I have using functions, it did the dollar sign expansion and nothing else.

Any ideas on what I need to do in the script to get my variable value to end up like it is in (1) above? Any input would be greatly appreciated because I need to get this project into source control but cannot do so if I'm going to lose these variable values.

Thanks!

Re: Source Control, Variables and Set Analysis

gksmithlcw — Fri, 12 Sep 2014 12:50:52 GMT

Nothing, ay?

Re: Re: Source Control, Variables and Set Analysis

Anonymous — Fri, 12 Sep 2014 12:57:25 GMT

Try a pair of single quotes around your dollar expansion.

=Sum({<EnrollmentDate_SD1 = {">='$(#vPlanYear_CurrentStart)'"}, ClmPdDt_MonthYear=, IncurFromDt_MonthYear=, EnrollmentStatus={'Employee'} >}[Medically Eligible Month])

Re: Source Control, Variables and Set Analysis

danieloberbilli — Fri, 12 Sep 2014 13:12:13 GMT

I think the dollar sign will always cause trouble when you set the variables in the script. I used a replace function therefore and a 2-step-approach for each variable...this should work

SET vVariable_tmp = "sum(~(x))";

SET vVariable = "=Replace(vVariable_tmp, '~', '$')";

Re: Source Control, Variables and Set Analysis

gksmithlcw — Fri, 12 Sep 2014 14:08:33 GMT

Thanks for the suggestion. Unfortunately, it had no effect.

Re: Source Control, Variables and Set Analysis

gksmithlcw — Fri, 12 Sep 2014 14:22:30 GMT

Daniel Oberbillig,

I changed my code as suggested to:

SET vPlanYear_Current_Employee_Months_temp = "Sum({<EnrollmentDate_SD1 = {">=~(#vPlanYear_CurrentStart)"}, ClmPdDt_MonthYear=, IncurFromDt_MonthYear=, EnrollmentStatus={'Employee'}>}[Medically Eligible Month])";

SET vPlanYear_Current_Employee_Months = "=Replace(vPlanYear_Current_Employee_Months_temp, '~', '$')";

This netted me a variable with the value of:

=Replace(vPlanYear_Current_Employee_Months_temp, '~', '$')

If I use this variable in a text object, I just get the text:

"Sum({<EnrollmentDate_SD1 = {">=$(#vPlanYear_CurrentStart)"}, ClmPdDt_MonthYear=, IncurFromDt_MonthYear=, EnrollmentStatus={'Employee'}>}[Medically Eligible Month])"

I also tried using LET in place of SET in the second line and that netted me an empty variable.

Re: Source Control, Variables and Set Analysis

danieloberbilli — Sat, 13 Sep 2014 00:00:19 GMT

Did you use the expression in the textbox like that: =$(vPlanYear_Current_Employee_Months) ?

Re: Source Control, Variables and Set Analysis

gksmithlcw — Sun, 14 Sep 2014 14:24:42 GMT

Yes, that's what yielded me the expression as text.

Re: Re: Source Control, Variables and Set Analysis

danieloberbilli — Sun, 14 Sep 2014 23:41:01 GMT

See qvw example attached.

It might be because of using the $() within the double quotes (what normally works fine)

In this case try it with single quotes like here:

SET vPlanYear_Current_Employee_Months_temp = "Sum({<EnrollmentDate_SD1 = {'>=$(vPlanYear_CurrentStart)'}, ClmPdDt_MonthYear=, IncurFromDt_MonthYear=, EnrollmentStatus={'Employee'}>}[Medically Eligible Month])";

Re: Source Control, Variables and Set Analysis

gksmithlcw — Mon, 15 Sep 2014 15:48:43 GMT

Daniel Oberbillig,

That did it! Thank you so much for the assistance.

That's a bit of a PITA but at least it gets the job done and gets my doc into source control which is a HUGE deal.

Re: Source Control, Variables and Set Analysis

danieloberbilli — Mon, 15 Sep 2014 23:02:06 GMT

Glad I could help.

Kind regards

Daniel