topic Re: Selecting a list of values from a QVD and using in SQL in QlikView

Selecting a list of values from a QVD and using in SQL

Anonymous — Wed, 19 Apr 2017 17:08:34 GMT

I'm looking to select a list of from a QVD and use the list of values in a SQL IN Clause.

For example if my data was the following:

LOAD * inline [

"ID|"First name"|"Last name"|Initials|"Has cellphone"

1|John|Anderson|JA|Yes

2|Sue|Brown|SB|Yes

3|Mark|Carr|MC |No

4|Peter|Devonshire|PD|No

5|Jane|Elliot|JE|Yes

6|Peter|Franc|PF|Yes ] (delimiter is '|');

I would want a variable that would look like ID_LIST(1,2,3,4,5,6)

Then I would take that variable and use it in my SQL:

SELECT * FROM PEOPLE WHERE ID in($ID_LIST);

I've been trying to use getfieldselections to get the data from my ID and getting an unknown statement error.

Any thoughts?

Re: Selecting a list of values from a QVD and using in SQL

Anil_Babu_Samineni — Wed, 19 Apr 2017 17:20:28 GMT

You can create variable like below

SET Var = 1,2,3,4,5;

Appraoch1:

LOAD * inline [

ID|First name|Last name|Initials|Has cellphone

1|John|Anderson|JA|Yes

2|Sue|Brown|SB|Yes

3|Mark|Carr|MC |No

4|Peter|Devonshire|PD|No

5|Jane|Elliot|JE|Yes

In you orient, You can try something like below

Appraoch2:

SELECT * FROM PEOPLE WHERE Match(ID, $(Var));

Re: Selecting a list of values from a QVD and using in SQL

Gysbert_Wassenaar — Wed, 19 Apr 2017 17:22:14 GMT

Try something like this:

MyTable:

LOAD Concat(ID, ',') as ID_List inline [

"ID|"First name"|"Last name"|Initials|"Has cellphone"

1|John|Anderson|JA|Yes

2|Sue|Brown|SB|Yes

3|Mark|Carr|MC |No

4|Peter|Devonshire|PD|No

5|Jane|Elliot|JE|Yes

6|Peter|Franc|PF|Yes ] (delimiter is '|');

LET vID_List = peek('ID_List',0,'MyTable');

MySQLTable:

SELECT * FROM PEOPLE WHERE ID in( $(vID_List) );

Re: Selecting a list of values from a QVD and using in SQL

Anonymous — Wed, 19 Apr 2017 17:32:51 GMT

How would the table statement change if I was selecting from a QVD as opposed to building the table inline?

Re: Selecting a list of values from a QVD and using in SQL

Gysbert_Wassenaar — Wed, 19 Apr 2017 17:38:59 GMT

Replace inline [ .... ] with FROM qvdname.qvd (qvd)