topic Re: ODAG generated where clause not being applied during data load in QlikView

ODAG generated where clause not being applied during data load

brandk — Sat, 16 Nov 2024 19:10:55 GMT

I am trying to implement On Demand app generation. I have created the Selection app and the Template app. I added the following script to the Template app (see script below). The problem is that when I make selections in Selection app, the where clauses show that they have been generated however they are not applied when the data loads.

//Creates a WHERE statement which can be used in table loads to restrict the data to a smaller set

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 //If WHERE clause exists then extend it with further values
LET WHERE_PART = '$(WHERE_PART) AND MATCH($(ColName),$(Values))';
ELSE
LET WHERE_PART = ' WHERE MATCH($(ColName),$(Values))'; //If WHERE clause does not exist then create it
ENDIF
ENDIF
END SUB;

//Here we set the variables ready to hold the names of any fields we want considering for ODAG filtering (selection)
//The ods_ field names bind the fields to those in your Selections Application.

SET Sel_Implementation = ; //resets the variable from any previous reload
SET Sel_Implementation = $(od_Implementation); // follow 'ods_' with the field name as it appears in your UI / final load
SET Sel_Implementation_COLNAME ='Implementation'; // enter the field name as it appears in the source data

SET Sel_Suffix= ;
SET Sel_Suffix = $(od_Suffix);
SET Sel_Suffix_COLNAME =Suffix;

SET WHERE_PART = '';

//This section builds the WHERE_PART variables that are passed to the SUB
//It extracts the values that have been selected from each field

FOR EACH fldname IN 'Sel_Implementation', 'Sel_Suffix'
LET vallist = $(fldname);
WHEN (IsNull(vallist)) LET vallist = '';
IF len(vallist) > 0 THEN
CALL ExtendWhere('$(fldname)','$(vallist)');
ENDIF
NEXT fldname

//Evaluate the WHERE_PART variable in the script for debugging
TRACE Generated WHERE clause: ;
TRACE $(WHERE_PART);

Re: ODAG generated where clause not being applied during data load

sunny_talwar — Tue, 04 Feb 2020 13:02:29 GMT

Where is the script where you applied the variable? Where clause variable ($(WHERE_PART)) looks to be good... may be it's implementation is not right?

Re: ODAG generated where clause not being applied during data load

brandk — Tue, 04 Feb 2020 13:34:34 GMT

@sunny_talwar Sorry, I am bit lost. Are you referring to the Selection App script?

Re: ODAG generated where clause not being applied during data load

sunny_talwar — Tue, 04 Feb 2020 14:08:08 GMT

Nope, still talking about the template app... after creating the Where clause... you are using this somewhere, right? and that somewhere is not honoring your Where clause? Isn't it? Can you share the script for that somewhere?

Re: ODAG generated where clause not being applied during data load

brandk — Tue, 04 Feb 2020 14:22:31 GMT

Okay ,I think that is where the problem lies.My understand of the ODAG functionality was that the generated where clause would be automatically injected into the script during loading. Is that not the case?

Re: ODAG generated where clause not being applied during data load

sunny_talwar — Tue, 04 Feb 2020 14:30:29 GMT

Well you need to apply this during your load or select statement... for example... let's say you are loading from qvd where you have both Implementation and Suffix field... this is what you will do

LOAD * From xyz.qvd (qvd) $(WHERE_PART);

The script is just creating the where clause for you... now you have to apply it according to your data...

Re: ODAG generated where clause not being applied during data load

brandk — Wed, 05 Feb 2020 10:53:30 GMT

Thanks a lot @Sunny_tawlar .That helped solved the issue.

Re: ODAG generated where clause not being applied during data load

sunny_talwar — Wed, 05 Feb 2020 12:52:17 GMT

Awesome

Re: ODAG generated where clause not being applied during data load

gordon_ash — Wed, 23 Aug 2023 13:08:57 GMT

Hi folks,

How do you get the diagnostics in your screen shot? I have trace statements to try and get the variables passed in but don't know how to view the outputs from the trace statement when it is an odag template. I am having problems with implementing the Where_Part and have no idea what is going into it,

thanks,

Gordon