Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
abirami_palanis
Contributor III
Contributor III

ODAG - Qliksense Template Application Code

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.

1 Solution

Accepted Solutions
abirami_palanis
Contributor III
Contributor III
Author

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);

View solution in original post

6 Replies
agigliotti
Partner - Champion
Partner - Champion

where is direct query in your script?

CELAMBARASAN
Partner - Champion
Partner - Champion

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.

abirami_palanis
Contributor III
Contributor III
Author

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.

abirami_palanis
Contributor III
Contributor III
Author

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

abirami_palanis
Contributor III
Contributor III
Author

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);

Kartik2
Partner - Creator
Partner - Creator

Hi, @abirami_palanis  

Can you post the qvf file of both odag template and Selection apps ??