Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
CUSTOMERS ONLY: Now accepting customer applications for the 2023 Luminary Program: SUBMIT NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
timothyj
Creator
Creator

ODAG Binding Passing Numeric Vales

I built a ODAG Selection and Template app. I was passing a field with numeric values and it worked well, until we got the On-demand error message about too many bound values. So I rewrote the script using the suggested changes. I kept getting errors. I used abirami.palanisamy  's script he suggested in this solution, but keep getting errors. I am pulling from QVDs, so tried to modify the script for that. I am stuck. I have tried changing the binding prefixes and value quotation and delimiter characters. I cannot get this to work. Below is the script. I would appreciate any help. With the below script I an getting:

"OdagBinding<<xxxxxxxxxx
Lines Fetched: 38 
_TempTable<<OdagBinding Lines Fetched: 1
1
Unexpected token: ',', expected on of: 'OPERATOR_PLUS', 'OPERATOR_MINUS', 'OPERATOR_MULTIPLICATION', 'OPERATOR_DIVISION', 'OPERATOR_STRING_CONCAT', 'like', 'and',  ..."

 

Thanks!

 

 

SUB ExtendWhere(Name, ValVarName)
LET T = Name & '_COLNAME';
LET ColName = $(T);
LET Values = $(ValVarName);
IF len(Values) > 0 THEN
IF len(WHERE_PART) > 0 THEN
LET WHERE_PART = '$(WHERE_PART) AND MATCH($(ColName),$(Values))';
ELSE
LET WHERE_PART = 'WHERE MATCH($(ColName),$(Values))';
ENDIF
ENDIF
END SUB;

 

SUB BuildValueList(VarName, TableName, ColName, QuoteChrNum)
IF $(QuoteChrNum) = 0 THEN
LET LOADEXPR = 'Concat($(ColName),' & chr(39) & ',' & chr(39) & ') AS CombinedData';
ELSE
LET CHREXPR = ' chr(' & '$(QuoteChrNum)' & ') ';
LET LOADEXPR = 'Concat( $(CHREXPR) & $(ColName) & $(CHREXPR)' & ',' & chr(39) & ',' & chr(39) & ') AS CombinedData';
ENDIF
_TempTable:
LOAD $(LOADEXPR) Resident $(TableName);
Let vNoOfRows = NoOfRows('_TempTable');
IF $(vNoOfRows)> 0 THEN
LET $(VarName) = Peek('CombinedData',0,'_TempTable');
ENDIF
drop table _TempTable;
drop table '$(TableName)';
END SUB;

SET ZZCFIID='';
OdagBinding:
LOAD * INLINE [
VAL
$(ods_ZZCFIID){"quote": "", "delimiter": ""}
];
SET ZZCFIID_COLNAME='ZZCFIID';
CALL BuildValueList('ZZCFIID', 'OdagBinding', 'VAL', 0);


SET WHERE_PART = '';
FOR EACH fldname IN 'ZZCFIID'
LET vallist = $(fldname);
WHEN (IsNull(vallist)) LET vallist = ''; //If Qlik suggests an error here ignore it
IF len(vallist) > 0 THEN
CALL ExtendWhere('$(fldname)','$(vallist)');
ENDIF
NEXT fldname

Labels (2)
1 Solution

Accepted Solutions
timothyj
Creator
Creator
Author

Solved it. I Replaced all the above code with the script below. I am a big fan of KISS. The odagn can be modified for text. I am only passing one field. If I needed more would repeat for WHERE_PART_2, 3, and so on.

 

OdagBinding:
LOAD * INLINE [
VAL
'$(odagn_ZZCFIID)'
];
SET ZZCFIID_COLNAME='ZZCFIID';
LET BINDLIST=Peek('VAL');
LET WHERE_PART = 'WHERE MATCH($(ZZCFIID_COLNAME),$(BINDLIST))';

Drop Table OdagBinding;

View solution in original post

3 Replies
NadiaB
Support
Support

Hi @timothyj

 

By any chance have you seen this already?

 

 

On-demand app error: The template has too many bound values for the standard approach

https://support.qlik.com/articles/000066859

 

Kind Regards.

Note: if this suggestion helps to resolve the issue or answers your question, it will be appreciated if you select the "Accept as Solution" option.
Visit our Knowledge Base https://community.qlik.com/t5/Knowledge/tkb-p/qlik-support-knowledge-base and our Qlik Help portal https://help.qlik.com/
timothyj
Creator
Creator
Author

Yes. If you look at the second to last section of script, I used the binding script that is in your link, with a change of 0 for 39 in the call for sub BuildValueList since I am bringing in numeric data.

timothyj
Creator
Creator
Author

Solved it. I Replaced all the above code with the script below. I am a big fan of KISS. The odagn can be modified for text. I am only passing one field. If I needed more would repeat for WHERE_PART_2, 3, and so on.

 

OdagBinding:
LOAD * INLINE [
VAL
'$(odagn_ZZCFIID)'
];
SET ZZCFIID_COLNAME='ZZCFIID';
LET BINDLIST=Peek('VAL');
LET WHERE_PART = 'WHERE MATCH($(ZZCFIID_COLNAME),$(BINDLIST))';

Drop Table OdagBinding;