Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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);
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);
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": ""}
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);
Thanks a lot abirami.. Its working...