topic Re: Setting dynamic Where conditions through variables in QlikView

Setting dynamic Where conditions through variables

Wed, 01 Feb 2012 11:43:33 GMT

Hi all,

I'm trying to create a huge SAP table looping on a specific query that will bring me an acceptable bunch of records and concatenate them until my table will be done.

So, my script-code looks like:

//BSIS_Soc_Ejer:
//SQL SELECT DISTINCT BUKRS GJAHR from BSIS;
// --> First I get all distinct combinations of the fields I'll use as where conditions. This table has been stored into a QVD.
set i=0;
Rows:
LOad *
from [~\DinamicWhere.QVD];
let NOrows=NoOfRows(Rows);
for i=1 to '$(NOrows)'
Trace $(i);
let BUKRS=FieldValue('BUKRS',$(i));
let GJAHR=FieldValue('GJARH',$(i));
Trace $(BUKRS);
Trace $(GJAHR);
BSIS_Complete:
NoConcatenate
SQL SELECT BUKRS HKONT GJAHR BELNR BUDAT BLDAT XBLNR BLART BSCHL SHKZG BUZEI FKONT WRBTR GSBER SGTXT VBUND ZFBDT SHKZG AUGBL AUGDT
FROM  BSIS 
WHERE  BUKRS=''$(BUKRS)'' AND GJAHR=''$(GJAHR)''
;
next
Store BSIS_Complete into [~\BSIS_Complete.QVD](qvd);
Drop Table BSIS_Complete;

But when I execute it, there's no value in my BUKRS and GJAHR fields.

Is there anything wrong? Trace shows me the value of "i", but Fieldvalue doesn't bring me anything...

Thansk for reading and thank for your probably future help in advance.

Re: Setting dynamic Where conditions through variables

Wed, 01 Feb 2012 12:15:33 GMT

I make it.

set i=0;
set BUKRS='';
Rows:
LOAD BUKRS
FROM
[~\BsisSocEjer.QVD]
(qvd)
;
let rows=NoOfRows('Rows');
Trace $(rows);
for i=1 to '$(rows)'
Trace $(i);
let BUKRS=FieldValue('BUKRS',$(i));
Trace $(BUKRS);
BSIS_Complete:
SQL SELECT *
FROM  BSIS 
WHERE  BUKRS = '$(BUKRS)'
;
next
Store BSIS_Complete into [~\BSIS_Completa.QVD](qvd);
Drop Table BSIS_Complete;

The key was bringing first fieldnames correctly.

After naming the field I was bringring in the first LOAD statement, FieldValue has been able to get the value correctly.

Thanks for reading, hope that this answer will help.

Aitor.

Re: Setting dynamic Where conditions through variables

Thu, 04 Dec 2014 15:01:06 GMT

Thanks, I've had this problem for a while now, and your solutions works fine.