How to use numeric Variable in Where Clause

[#1]

Hello,

I set up a Variable as : SET DataEntryID = '3,30' in the main tap in script;  The reason I gave more than one values here, is becuase the value may change when migrating data from other system from time to time. So today the DataEntryID is 3, but tomorrow the ID could be 30. Therefore, I can easily change the value in Variable, instead of  change the code/script from place to place. And I want to keep both values, or even more than 2 values in the variable.

What I am interested in is to use the this Vriable list in WHERE Clause. I did this:

LOAD
Case_ID,
FR_STATE_ID,
TO_STATE_ID

From Table name

Where TO_STATE_ID <= DataEntry;

However, this doesn't work. I am thinking it maybe the syntax was wrong? Please kindly let me know how I can make it work.

Thank you in advance for your help,

Becky

[Peter_Cammaert]

A variable can contain only one numerical value at any time. Make up your mind and try loading again.

Why would you store more than one value in this variable if all except one don't apply at a particular time? And if you do not like changing your script, let your script set the variable only in case it doesn't already exist. That way you can set/change the variable in your document using QV Desktop Variable overview, or using the parameters fields in your publisher task definition.

Best,

Peter

[#3]

Thanks Peter for your time and input.

The reason I may store more than value in this variable, is because there could be different values in DataEntry field. For example, I have 3 as State_ID today, but I could have value 30 as State_ID for the same state in the future after migrate data from different system. And I need to show all records under same STATE. So it would be those data associate with 3 (old data) and 30 (new data).  I know this doesn't sound make sense. But just in case it would happen. So does this mean, there is no other way to acchieve my goal, if a variable can only contain one numerical value?

Best,

Becky

[Peter_Cammaert]

On the contrary, there are ways to do almost anything in QlikView script. Data-related, that is.

But I'm not sure that I understand your request. You're referring to State_ID as possibly having multiple current and/or future values. Do you want to filter your data source on particular State_ID values? Because as far as I can see, your WHERE clause tries to do entirely different things. And there seem to be two State_ID fields in your data source. Which one do you want to check?

Peter

[oscar_ortiz]

I believe you're looking to do something like this:

LOAD

Case_ID,
FR_STATE_ID,
TO_STATE_ID

Where

  Match( TO_STATE_ID, $(vDataEntry) )

;

SQL SELECT *

FROM YourTable

;

Note that I have my where clause as part of my preceding load statement.

[Peter_Cammaert · Accepted solution]

This is just a guess based on assumptions. Could it be something like this you're looking for?

SET AllowedStates = 3, 30; // Stores a parameter list as a text string in variable

LOAD Case_ID,
    FR_STATE_ID,
    TO_STATE_ID

FFROM Table name

WHERE match(TO_STATE_ID, $(AllowedStates)); // Expand parameter list in-place

You can make the list of acceptable values as long as you want.

Best,

Peter

[#7]

Thanks Peter.

My post has typo. That's why you got confused. Sorry about that. I just updated it. The one I want to check is TO_STATE_ID.

There are two STATE_IDS in total. One is FR_STATE_ID (you can see it as from state_id), the other is To_STATE_ID.

There are different values in TO_STATE_ID, each value associate to each State_type. For example, I have 1, 2, 3, 4 values in TO_STATE_ID. 1 stands for State_type "Initial date", 2 stands fo State_type "Follow up date", 3 stands for "Data entry date" etc.

Currently, each State_type only associate with one TO_STATE_ID. But in the future, each state_type may associate with more than one TO_STATE_ID.

Does this answer your questions? Please let me know what I can be more clear about it.

Best,

Becky

[#8]

Hi Oscar,

Thank you so much for providing the solution.

I tried your way. But it shows error after I ran it. Then I tried Peter's, it works. The only different between Peter and your solution is Match( TO_STATE_ID, $(vDataEntry) ), the "v". I am not sure why as I've seen the "v" from Qlik Developer book. I appreaicate your help.

Best,

Becky

[#9]

Thanks Peter, this works!

[oscar_ortiz]

The"v" is based on naming conventions.  I like to prefix my variables with "v".