Help on On demand app

[bhavvibudagam]

Hi Experts,

Can any one please help me on below requirement.

Is it possible to apply the ODAG binding expressions on one fact table and one dimension table as seperate.

Suppose there are two tables Products and Hours like below.Here Need to apply ODAG binding expression on START DATE from Products table and  STATUS,POSFLAG fields from Hours table is it possible or not please confirm.

I have added some example code

SET STARTDATE='';

OdagBinding:

LOAD * INLINE [

VAL

$(ods_STARTDATE){"quote": "", "delimiter": ""}

];

SET STARTDATE_COLNAME='STARTDATE';

CALL BuildValueList('STARTDATE', 'OdagBinding', 'VAL', 39); // 39 is for single quote wrapping values

//Where Clause

SET WHERE_PART = '';

FOR EACH fldname IN 'STARTDATE'

  LET vallist = $(fldname);

  WHEN (IsNull(vallist)) LET vallist = '';

  IF len(vallist) > 0 THEN

    CALL ExtendSqlWhere('$(fldname)','vallist');

  ENDIF

NEXT fldname

TRACE Generated WHERE clause: ;

TRACE $(WHERE_PART);

SET STATUS='';

OdagBinding:

LOAD * INLINE [

VAL

$(ods_STATUS){"quote": "", "delimiter": ""}

];

SET STATUS_COLNAME='STATUS';

CALL BuildValueList('STATUS', 'OdagBinding', 'VAL', 39); // 39 is for single quote wrapping values

SET POSFLAG='';

OdagBinding:

LOAD * INLINE [

VAL

$(ods_POSFLAG){"quote": "", "delimiter": ""}

];

SET POSFLAG_COLNAME='POSFLAG';

CALL BuildValueList('POSFLAG', 'OdagBinding', 'VAL', 39); // 39 is for single quote wrapping values

//Where Clause

SET WHERE_PART1 = '';

FOR EACH fldname IN 'STATUS','POSFLAG'

  LET vallist = $(fldname);

  WHEN (IsNull(vallist)) LET vallist = '';

  IF len(vallist) > 0 THEN

    CALL ExtendSqlWhere('$(fldname)','vallist');

  ENDIF

NEXT fldname

TRACE Generated WHERE clause: ;

TRACE $(WHERE_PART1 );

Products:

Load

STARTDATE,

ID,

SALES;

Select

STARTDATE,

ID,

SALES

from ....

$(WHERE_PART);

Hours:

Load

EndDate,

STATUS,

POSFLAG;

SELECT

ID,

EndDate,

STATUS,

POSFLAG

From......

$(WHERE_PART1);

[petter]

The short answer is YES.

There are no limitations in the ODAG functionality/methodology that prevents you from having selections from any field from any of the tables of the selection app. Qlik Sense does not in the data model have any notion whether a table is a fact table or a dimension table.

There are a syntax errors in your load script that you need to fix. You have forgot an ending paranthesis/ellipses/bracket after the fieldname and before the beginning curly braces/brackets in all the three INLINE sections:

$(ods_STARTDATE{"quote": "", "delimiter": ""}   

    should be    

$(ods_STARTDATE){"quote": "", "delimiter": ""}

$(ods_STATUS{"quote": "", "delimiter": ""}

    should be    

$(ods_STATUS){"quote": "", "delimiter": ""}

$(ods_POSFLAG{"quote": "", "delimiter": ""}

    should be    

$(ods_POSFLAG){"quote": "", "delimiter": ""}

[abirami_palanis · Accepted solution]

Yes It is possible bhavani..

Please see the below code. This will work as you wanted.

SUB ExtendSQLWhere(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;

SUB ExtendSQLWhere1(Name, ValVarName)

  LET T = Name & '_COLNAME';

  LET ColName = $(T);

  LET Values = $(ValVarName);

  IF len(Values) > 0 THEN

  IF len(WHERE_PART1) > 0 THEN

    LET WHERE_PART1 = '$(WHERE_PART1) AND $(ColName) IN ( $(Values) )';

    ELSE

    LET WHERE_PART1 = 'WHERE $(ColName) IN ( $(Values) )';

    ENDIF

  ENDIF

END SUB;

// DO NOT ALTER THIS SUBROUTINE

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 STARTDATE='';

OdagBinding:

LOAD * INLINE [

VAL

$(ods_STARTDATE){"quote": "", "delimiter": ""}

];

SET STARTDATE_COLNAME='STARTDATE';

CALL BuildValueList('STARTDATE', 'OdagBinding', 'VAL', 39); // 39 is for single quote wrapping values

SET WHERE_PART = '';

FOR EACH fldname IN 'STARTDATE'

LET vallist = $(fldname);

WHEN (IsNull(vallist)) LET vallist = '';

IF len(vallist) > 0 THEN

CALL ExtendSqlWhere('$(fldname)','vallist');

ENDIF

NEXT fldname

TRACE Generated WHERE clause: ;

TRACE $(WHERE_PART);

SET STATUS='';

OdagBinding:

LOAD * INLINE [

VAL

$(ods_STATUS){"quote": "", "delimiter": ""}

];

SET STATUS_COLNAME='STATUS';

CALL BuildValueList('STATUS', 'OdagBinding', 'VAL', 39); // 39 is for single quote wrapping values

SET POSFLAG='';

OdagBinding:

LOAD * INLINE [

VAL

$(ods_POSFLAG){"quote": "", "delimiter": ""}

];

SET POSFLAG_COLNAME='POSFLAG';

CALL BuildValueList('POSFLAG', 'OdagBinding', 'VAL', 39); // 39 is for single quote wrapping values

SET WHERE_PART1 = '';

FOR EACH fldname IN 'POSFLAG','STATUS'

  LET vallist = $(fldname);

  WHEN (IsNull(vallist)) LET vallist = '';

  IF len(vallist) > 0 THEN

    CALL ExtendSQLWhere1('$(fldname)','vallist');

  ENDIF

NEXT fldname

TRACE Generated WHERE clause: ;

TRACE $(WHERE_PART1);

Products:

Load STARTDATE,ID,SALES;

Select STARTDATE,ID,SALES from ....

$(WHERE_PART);

Hours:

Load EndDate,STATUS,POSFLAG;

SELECT ID,EndDate,STATUS,POSFLAG

From......

$(WHERE_PART1);

[bhavvibudagam]

Thanks a lot abirami.. Its working...