topic Re: How to use where match statement in load in QlikView

How to use where match statement in load

Mon, 28 Nov 2016 19:32:25 GMT

SQL SELECT "QUESTION_ANSWER_ID",
MSID,
"QUESTION_ID",
ANSWER
FROM IQREVDB.TBQUESTIONANSWERS

Where "QUESTION__ID" = 358 or
"QUESTION__ID" = 72 or
"QUESTION__ID" = 73 or
"QUESTION__ID" = 75 or
"QUESTION__ID" = 76 or
"QUESTION__ID" = 78 or
"QUESTION__ID" = 79 or
"QUESTION__ID" = 95 or
"QUESTION__ID" = 96 or
"QUESTION__ID" = 97 or
"QUESTION__ID" = 98 or
"QUESTION__ID" = 99 or
"QUESTION__ID" = 100 or
"QUESTION__ID" = 101 or
"QUESTION__ID" = 102 or
"QUESTION__ID" = 103 or
"QUESTION__ID" = 104 or
"QUESTION__ID" = 105 or
"QUESTION__ID" = 106 or
"QUESTION__ID" = 107 or
"QUESTION__ID" = 108 or
"QUESTION__ID" = 109 or
"QUESTION__ID" = 110 or
"QUESTION__ID" = 140 or
"QUESTION__ID" = 141 or
"QUESTION__ID" = 142 or
"QUESTION__ID" = 158 or
"QUESTION__ID" = 161 or
"QUESTION__ID" = 162 or
"QUESTION__ID" = 163 or
"QUESTION__ID" = 164 or
"QUESTION__ID" = 166 or
"QUESTION__ID" = 199 or
"QUESTION__ID" = 200 or
"QUESTION__ID" = 221 or
"QUESTION__ID" = 237 or
"QUESTION__ID" = 239 or
"QUESTION__ID" = 240 or
"QUESTION__ID" = 241 or
"QUESTION__ID" = 242 or
"QUESTION__ID" = 243 or
"QUESTION__ID" = 244 or
"QUESTION__ID" = 249 or
"QUESTION__ID" = 298 or
"QUESTION__ID" = 318 or
"QUESTION__ID" = 319;

Can someone edit my script so that I don't receive the error "invalid identifier"? I am trying to load lines from the field "QUESTION_ID" that correspond to a list like: 75,76,77,78,79,etc.

Thanks

Re: How to use where match statement in load

its_anandrjs — Mon, 28 Nov 2016 19:35:16 GMT

Try where exists function rather that match.

Regards

Anand

Re: How to use where match statement in load

Mon, 28 Nov 2016 19:39:25 GMT

I am not familiar with that function. Could you give a brief example?

Re: How to use where match statement in load

sunny_talwar — Mon, 28 Nov 2016 19:40:42 GMT

You can use SQL's IN statement

SQL SELECT "QUESTION_ANSWER_ID",
MSID,
"QUESTION_ID",
ANSWER
FROM IQREVDB.TBQUESTIONANSWERS
Where "QUESTION__ID" IN (358, 72, 73, 75 , 76, 78, 79, ...);

Re: How to use where match statement in load

its_anandrjs — Mon, 28 Nov 2016 19:41:31 GMT

Use from statement in the SQL load statement

QuidTab:

LOAD * Inline

[

QueID

358

100

101

102

103

104

105

106

107

108

109

110

140

141

142

158

161

162

163

164

166

199

200

221

237

239

240

241

242

243

244

249

298

318

319

];

Final:

LOAD

"QUESTION_ANSWER_ID",

MSID,

"QUESTION_ID",

ANSWER

Where Exists(QueID,"QUESTION_ANSWER_ID");

SQL SELECT "QUESTION_ANSWER_ID",

MSID,

"QUESTION_ID",

ANSWER

FROM IQREVDB.TBQUESTIONANSWERS

Note:- Load your Queid in separate table and then load only those que in the final table with where exists function.

Regards

Anand

Re: How to use where match statement in load

Mon, 28 Nov 2016 19:51:33 GMT

I tried this and it did not work:

SQL SELECT "QUESTION_ANSWER_ID",
MSID,
"QUESTION_ID",
ANSWER
FROM IQREVDB.TBQUESTIONANSWERS

Where "QUESTION__ID" IN(358,72,73,75,76,78,79,95,96,97,98,99,100,101,102,103,104,105,106,107,108,109,110,140,141,142,158,161,162,163,164,166,199,200,221,237,239,240,241,242,243,244,249,298,318,319);

Re: How to use where match statement in load

sunny_talwar — Mon, 28 Nov 2016 20:04:12 GMT

What error did you get when you tried running the above? Is there two underscores or one underscore between Question and ID?

SQL SELECT "QUESTION_ANSWER_ID",

MSID,

"QUESTION_ID",

ANSWER

FROM IQREVDB.TBQUESTIONANSWERS

Where "QUESTION__ID" IN (358, 72, 73, 75 , 76, 78, 79, ...);

SQL SELECT "QUESTION_ANSWER_ID",

MSID,

"QUESTION_ID",

ANSWER

FROM IQREVDB.TBQUESTIONANSWERS

Where "QUESTION_ID" IN (358, 72, 73, 75 , 76, 78, 79, ...);

Re: How to use where match statement in load

its_anandrjs — Mon, 28 Nov 2016 20:07:45 GMT

Please make correction on the script

Final:

LOAD

"QUESTION_ANSWER_ID",

MSID,

"QUESTION_ID",

ANSWER

Where Exists(QueID,"QUESTION_ID");

SQL SELECT "QUESTION_ANSWER_ID",

MSID,

"QUESTION_ID",

ANSWER

FROM IQREVDB.TBQUESTIONANSWERS