topic Re: Use Variables in Partial Reload in QlikView

Use Variables in Partial Reload

jzimolong — Thu, 06 Nov 2014 22:09:25 GMT

I've been trying many different things based on research in the community, but figured I would post my own question on this.

I have three variables, which I set the values as follows:

vSelectedVer: =GetFieldSelections(ver,', ',20)

vFirstVer: =SubField(vSelectedVer,', ',1)

vSecondVer: =SubField(vSelectedVer,', ',2)

I have a field "ver" in my app, and a corresponding listbox with following values:

20140930

20140831

20140731

20140630

I want user to select two of the values. So if they select 20140930 and 20140630, the variables look like this:

vSelectedVer: 20140930, 20140630

vFirstVer: 20140930

vSecondVer: 20140630

I can use these in charts, part of set analysis to do different displays and calculations.

What I have now been trying to do is use these values in a REPLACE LOAD script. I want to run partial, so I can pull a subset of the RESIDENT table, and then I can bring the subset in to make a pivot chart. The resident table carries a portfolio of accounts, each with a different "ver" or monthend identifier. Here is what my REPLACE LOAD script looks like. I haven't made it complex, as I'm just trying to get it to work using variable first.

PDMigration:

REPLACE LOAD

ver

, RootObgNo

, RootOblNo

, PDGrade as CurPD

RESIDENT vCRS_RLD

WHERE ver = $(vFirstVer) ;

Whenever I try it, I get the following script error:

Field not found - <=>

PDMigration:

REPLACE LOAD

ver

, RootObgNo

, RootOblNo

, PDGrade as CurPD

RESIDENT vCRS_RLD

WHERE ver = =SubField(vSelectedVer,', ',1)

Re: Use Variables in Partial Reload

vgutkovsky — Thu, 06 Nov 2014 22:18:59 GMT

Joseph, I think the easiest way to accomplish what you need is to create a temporary variable in your script. Try this:

LET vFirstVerB = replace(vFirstVer,'=','');

PDMigration:

REPLACE LOAD

ver

, RootObgNo

, RootOblNo

, PDGrade as CurPD

RESIDENT vCRS_RLD

WHERE ver = $(vFirstVerB);

LET vFirstVerB = null();

Re: Use Variables in Partial Reload

jzimolong — Thu, 06 Nov 2014 22:25:08 GMT

Thanks for the suggestion. I made the updates and I still get the following error in the script:

Field not found - <vSelectedVer>

PDMigration:

REPLACE LOAD

ver

, RootObgNo

, RootOblNo

, PDGrade as CurPD

RESIDENT vCRS_RLD

WHERE ver = SubField(vSelectedVer,', ',1)

Re: Use Variables in Partial Reload

vgutkovsky — Thu, 06 Nov 2014 23:07:21 GMT

My mistake, the above won't work for your type of variable expression. The only way (that I can see) to do it is through adding triggers. In your case, open Document Properties >> Triggers >> Field Event Triggers >> ver. Press the "Add Action" button under OnSelect. Press Add >> External >> Set Variable. For the variable name, enter vFirstVerB. For the variable value, enter =vFirstVer

Create the same trigger for OnChange.

Then your load script can be as I wrote above except for setting vFirstVerB.

PDMigration:

REPLACE LOAD

ver

, RootObgNo

, RootOblNo

, PDGrade as CurPD

RESIDENT vCRS_RLD

WHERE ver = $(vFirstVerB);

It's not very elegant, but I honestly can't think of another way of doing it.

Vlad

Re: Use Variables in Partial Reload

jzimolong — Thu, 06 Nov 2014 23:20:03 GMT

Thank you Vlad, this appears to be working! I can now further expand the script. Thanks again, this gets me over the first hurdle.