Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Everyone,
I want to create a ODAG application that will restrict one period , one geography , multiple product values. and i am using direct discovery to pull the data.
I am using below mentioned code. it is working fine with normal extraction code. but when i am using Direct discovery method this code is not working.
Can anyone please help me.
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 $(ColName) IN ( $(Values) )';
ELSE
LET WHERE_PART = ' WHERE $(ColName) IN ( $(Values) )';
ENDIF
ENDIF
END SUB;
SET Period = ;
SET Period = $(odso_Period);
SET Period_COLNAME='Period';
SET Geography = ;
SET Geography = $(odso_Geography);
SET Geography_COLNAME='Geography';
SET PROD = ;
SET PROD = $(odso_PROD);
SET PROD_COLNAME='PROD';
SET WHERE_PART = '';
FOR EACH fldname IN 'Period', 'Geography','PROD'
LET vallist = $($(fldname));
WHEN (IsNull(vallist)) LET vallist = '';
IF len(vallist) > 0 THEN
CALL ExtendWhere('$(fldname)','vallist');
ENDIF
NEXT fldname
TRACE Generated WHERE clause: ;
TRACE $(WHERE_PART);
Thank you in advance.
Hi ,
I have used below mentioned code. It is working as expected.
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 ($(ColName) IN ( $(Values))) ';
ELSE
LET WHERE_PART = 'WHERE ($(ColName) IN ( $(Values) ))';
ENDIF
ENDIF
END SUB;
SET Period = ;
SET Period = $(odso_Period);
SET Period_COLNAME='Period';
SET Geography = ;
SET Geography = $(odso_Geography);
SET Geography_COLNAME='Geography';
SET PROD = ;
SET PROD = $(odso_PROD);
SET PROD_COLNAME='PROD';
SET WHERE_PART = '';
FOR EACH fldname IN 'Period', 'Geography','PROD'
LET vallist = $($(fldname));
WHEN (IsNull(vallist)) LET vallist = '';
IF len(vallist) > 0 THEN
CALL ExtendWhere('$(fldname)','vallist');
ENDIF
NEXT fldname
TRACE Generated WHERE clause: ;
TRACE $(WHERE_PART);
where is direct query in your script?
Hi Abirami
ODAG is different from Direct discovery, You can't use ODAG parameter values in Direct Query, Since direct query will run the script every time you select a values in dimension.
ODAG parameters are passed by ODAG extension to the Qlik Engine to create dynamic application. If you are using Direct query without ODAG parameters, it will work.
Hi celambarasan,
Actually when i am selecting single values this code is working even with direct discovery. but the problem is whenever i am selecting more than one value it is not taking that field restriction itself.
I know that ODAG and Direct discovery are different. My requirement is, I have to implement ODAG with
direct discovery.
Thank you for the response.
Hi Andrea,
please find the query
direct QUERY
dimension
abc.Period as Period ,
abc.Geography as Geography ,
abc.PROD as PROD
measure
abc.volume as volume,
abc.count as prod_count
from abc
$(WHERE_PART);
Thank you
Hi ,
I have used below mentioned code. It is working as expected.
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 ($(ColName) IN ( $(Values))) ';
ELSE
LET WHERE_PART = 'WHERE ($(ColName) IN ( $(Values) ))';
ENDIF
ENDIF
END SUB;
SET Period = ;
SET Period = $(odso_Period);
SET Period_COLNAME='Period';
SET Geography = ;
SET Geography = $(odso_Geography);
SET Geography_COLNAME='Geography';
SET PROD = ;
SET PROD = $(odso_PROD);
SET PROD_COLNAME='PROD';
SET WHERE_PART = '';
FOR EACH fldname IN 'Period', 'Geography','PROD'
LET vallist = $($(fldname));
WHEN (IsNull(vallist)) LET vallist = '';
IF len(vallist) > 0 THEN
CALL ExtendWhere('$(fldname)','vallist');
ENDIF
NEXT fldname
TRACE Generated WHERE clause: ;
TRACE $(WHERE_PART);
Hi, @abirami_palanis
Can you post the qvf file of both odag template and Selection apps ??