On Demand App issue

[bhavvibudagam]

Hi,

kuczynska

saurabhwadhwa

erik.wetterberg

abirami.palanisamy

sureshqv

omarbensalem

Could you please help  me on below On demand Generation App issue.

I am able to generate the  on demand apps from summary app but the selections are not applying in the generated On Demand apps getting total data.

Could you please check the below Template app script once. Please let me know am i missed anything.

1. I have created summary app like below

By reloading getting 10000 records

Main:

LOAD

    APPLICATION,

    CustomerKey,

    SalesOrderNumber,

    SalesOrderLineNumber,

    OrderQuantity,

    DiscountAmount,

    SalesAmount,

    TaxAmt,

    Freight,

    OrderDate,

    ShipDate

FROM [lib://IT Support/1.QVD\1.Extract\A1779.qvd] (qvd);

2. Then created Template App

By reloading getting 0 records. I think we no need to reload the Template app its mentioned in Qlik Help also.

// DO NOT ALTER THIS SUBROUTINE

SUB ExtendQVDWhere(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 mixmatch([$(ColName)],$(Values) )';

    ELSE

        LET WHERE_PART = ' WHERE mixmatch([$(ColName)],$(Values))';

    ENDIF

  ENDIF

END SUB;

// DO NOT ALTER THIS SUBROUTINE

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;

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

//binding Expression

SET App='';

OdagBinding:

LOAD * INLINE [

VAL

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

];

SET APP_COLNAME='APPLICATION';

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

//Where Clause

SET WHERE_PART = '';

FOR EACH fldname IN 'APPLICATION'

  LET vallist = $(fldname);

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

  IF len(vallist) > 0 THEN

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

  ENDIF

NEXT fldname

TRACE Generated WHERE clause: ;

TRACE $(WHERE_PART);

//QVD

Main:

LOAD

    APPLICATION,

    CustomerKey,

    SalesOrderNumber,

    SalesOrderLineNumber,

    OrderQuantity,

    DiscountAmount,

    SalesAmount,

    TaxAmt,

    Freight,

    OrderDate,

    ShipDate

FROM [lib://IT Support/1.QVD\1.Extract\Slaes.qvd] (qvd)

$(WHERE_PART);

But in the Generated app it contains the total data instead of selected data. Please let me know Am i missed anything.

Thanks in advance.

[abirami_palanis]

Hi Bhavani,

If you want to show only the selected values data in Template application please use $(ods_APPLICATION) instead of $(odo_APPLICATION) .

$(odo_APPLICATION) - will take only the optional values of application not the selected values.

Pattern	Expansion
$(ods_REGION_CODE)	Selected (green) values of REGION_CODE
$(odo_REGION_CODE)	Optional (white) values of REGION_CODE
$(odso_REGION_CODE)	Selected or optional values of REGION_CODE
$(od_REGION_CODE)	Same as $(ods_REGION_CODE), only selected (green) values of REGION_CODE

Please use below code as binding expression.

//binding Expression

SET APPLICATION ='';

OdagBinding:

LOAD * INLINE [

VAL

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

];

SET APPLICATION_COLNAME='APPLICATION';

CALL BuildValueList('APPLICATION', 'OdagBinding', 'VAL', 39);

I hope this will help you !

[bhavvibudagam]

Hi,

Thanks for your reply.

Could you please clarify my doubt.

When I have loaded the Template app its fetching zero records.

Do we need to reload the Template app or not please confirm.

[abirami_palanis]

hi,

No need to reload template application. Just model it however you want.

Then from selection application , you can reload your template app for the selected values by generating new application.

[abirami_palanis]

Hi Bhavani ,

As we discussed , I found a way to pass Date as well as other filters. Please see the below code.

// GENERATING WHERE FOR DATE

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  ( $(Values) )';

    ELSE

    LET WHERE_PART = '( $(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 DATE ='';

OdagBinding:

LOAD * INLINE [

VAL

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

];

SET DATE_COLNAME='DATE';

CALL BuildValueList('DATE', 'OdagBinding', 'VAL', 39);

SET WHERE_PART = '';

FOR EACH fldname IN 'DATE'

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

// GENERATING WHERE FOR OTHER FIELDS

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;

SUB BuildValueList1(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 TYPE ='';

OdagBinding:

LOAD * INLINE [

VAL

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

];

SET TYPE_COLNAME='TYPE';

CALL BuildValueList1('TYPE', 'OdagBinding', 'VAL', 39);

SET ID ='';

OdagBinding:

LOAD * INLINE [

VAL

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

];

SET ID_COLNAME='ID';

CALL BuildValueList1('ID', 'OdagBinding', 'VAL', 39);

SET WHERE_PART1 = '';

FOR EACH fldname IN 'TYPE','ID'

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

SQL

select

distinct

ID,

TYPE,

to_char(DATE,'DD/MM/YYYY') as DATE

from abc

$(WHERE_PART1)

and to_char(DATE,'DD/MM/YYYY') in ($(WHERE_PART))

I hope this will work as you wanted.

[bhavvibudagam]

Hi Abirami,

Thanks alot for your help. but its still not working in my SQL query.

As you said earlier I have tested the date load manually.

First I have tested like this where to_char(p.date,'DD/MM/YYYY')  in ('02/02/2018') but its not loading the data.

so changed like below where p.date in  to_date('02/02/2018','DD/MM/YYYY'); then its loading the data.

Manual Test:

Load

DATE,

STATUS,

TYPE,

POSFLAG

ID;

Sql Select

to_char(p.date,'DD/MM/YYYY') as date,

h.status,

h.posflag

from product p

inner join hours h on h.systemid = p.systemid and h.key=p.key

where p.date in  to_date('02/02/2018','DD/MM/YYYY');

Next passed where part like where to_char(p.date,'DD/MM/YYYY')  in ($(WHERE_PART)); its not working

then tried where p.date in to_date($(WHERE_PART)); its also not working

My SQL Table is like below.

Load

DATE,

STATUS,

TYPE,

POSFLAG

ID;

Sql Select

to_char(p.date,'DD/MM/YYYY') as date,

h.status,

h.posflag

from product p

inner join history h on h.systemid = p.systemid and h.key=p.key

where p.date  in  to_date($(WHERE_PART));

Is ODAG supports the inner join and sql query with fields like p.date,h.status . Please confirm.

For DATE I have used the below ODAG expression

SET DATE ='';

OdagBinding:

LOAD * INLINE [

VAL

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

];

SET DATE_COLNAME='DATE';

CALL BuildValueList('DATE', 'OdagBinding', 'VAL', 39);

[bhavvibudagam]

Hi abirami.palanisamy,

I have found the issue where it is. Could you please check the below points to help me.

For Testing purpose I have extracted the hours database table from SQL data connection without any joins i have tested only one table for checking date issue.

In manual test the table is loading with below condition

lib connect to ....

LOAD

DATE

STATUS

POSFLAG;

Sql Select

to_char(DATE,'DD/MM/YYYY')  as DATE,

STATUS,

POSFLAG

where to_char(DATE,'DD/MM/YYYY')  in '02/02/2018';  .....Here without parenthesis ('02/02/2018') like '02/02/2018' is loading

By using below ODAG code the generate where clause is like DATE in ('02/02/2018')

next in the end of table when we pass to_char(DATE,'DD/MM/YYYY') in ($(WHERE_PART)) then its like

to_char(DATE,'DD/MM/YYYY') in (DATE in ('02/02/2018')) ---- so getting error

at $(WHERE_PART) need to pass only '02/02/2018' .Please help me to pass only selected date value under $(WHERE_PART).

SET DATE ='';

OdagBinding:

LOAD * INLINE [

VAL

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

];

SET DATE_COLNAME='DATE';

CALL BuildValueList('DATE', 'OdagBinding', 'VAL', 39);

SET WHERE_PART = '';

FOR EACH fldname IN 'DATE'  --- by this loop getting DATE in ('02/02/2018') is it possible to pass selected value without loop in                                                                              WHERE_PART

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

Thanks in advance.

[abirami_palanis]

Yes , It's supports all kind of joins. You can also pass where conditions like P.Date in ('01/01/2018') to SQL queries by using below code.

SET Date ='';

OdagBinding:

LOAD * INLINE [

VAL

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

];

SET Date_COLNAME='P.Date';

CALL BuildValueList('Date', 'OdagBinding', 'VAL', 39);

[abirami_palanis]

Hi Bhavani ,

you can use below code to pass only values in where condition.

The generated where condition will pass only the values like below.

If you select only one Date value ---- > ('02/02/2018')

If you select more than one Date values -----> ('02/02/2018','03/03/2018')

// Code to pass only values

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  ( $(Values) )';

    ELSE

    LET WHERE_PART = '( $(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 DATE ='';

OdagBinding:

LOAD * INLINE [

VAL

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

];

SET DATE_COLNAME='DATE';

CALL BuildValueList('DATE', 'OdagBinding', 'VAL', 39);

SET WHERE_PART = '';

FOR EACH fldname IN 'DATE'

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

lib connect to ....

LOAD

DATE

STATUS

POSFLAG;

Sql Select

to_char(DATE,'DD/MM/YYYY')  as DATE,

STATUS,

POSFLAG

where to_char(DATE,'DD/MM/YYYY')  in $(WHERE_PART);

You mentioned without parenthesis only where condition is  working. But for me with parenthesis also it is working.

In this case when you want to pass more than one date value to where  how it will work without parenthesis ?

If your requirement is to select only one date and you don't want to pass values with parenthesis use below sub part.

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  ($(Values) )';

    ELSE

    LET WHERE_PART = ' $(Values) ';

    ENDIF

  ENDIF

END SUB;

[bhavvibudagam]

Hi Abirami,

Thanks for your reply.

My requirement is to pass only one selected date like this where to_char(DATE,'DD/MM/YYYY')  in '02/02/2018';