error in qlik

[Manni_SM]

Hi All,

im converting oracle code to Qlik script but am getting error while running this query

Transactions:

SQL SELECT 

    Transactions.report_key AS REPORT_KEY, 

    Transactions.transaction_date AS TRANSACTION_DATE_TIME, 

    SUM(Transactions.ctr_cash_in + Transactions.ctr_cash_out) AS Transaction_Amount,

    Transactions.ctr_tin_for_dep AS Transaction_Sequence_Number

FROM 

    ctr_main.ctr_rpt_transactions Transactions

INNER JOIN 

    ctr_main.mta_relation MTA_Transactions ON Transactions.tin_for_doep = MTA_Transactions.tin

WHERE

transaction_date>='$(vStartdate)' and transaction_date<='$(vEnddate)'   //  tried in qlik

//     Transactions.transaction_date BETWEEN :start_date AND :end_date  // original coloum

    AND Transactions.ctr_aggregator_key IS NOT NULL

    AND Transactions.tin_for_doep IS NOT NULL

GROUP BY 

    Transactions.report_key,

    Transactions.ctr_aggregator_key,

    MTA_Transactions.group_branch,

    MTA_Transactions.group_number;

Tempdates:  

load  min(transaction_date) as startdate,

max(transaction_date) as Enddate  

resident Transactions;

let vStartdate=peek('startdate',0,'Tempdates');

let vEnddate=peek('Enddate',0,'Tempdates');

drop table Tempdates;

i have tried in bold letter script but its not loading failed with  attached error .can anyone help 

[Or]

Based on the error, your date variables aren't getting populated or read correctly. Run in debug mode to find out exactly what's happening with those and fix the issue. 

[Manni_SM]

thank you is my approach is correct? 

and one more doubt - in UI  i need to use daterange filter   start date and end date

so instead of where condition shall i use directly  min(trandate)  and max(trandate)  in daterange filter object?

[marcus_sommer]

The transactions are defining the dates and are in the same time their filter-condition. Such logic could be working if there are manual set values for the first initial load.

Beside this I'm not sure if the global defined dates are the same as the in the between referenced dates - are they also global or have they a context to any key-fields? Similar - what is meant with the date-ranges? Linked weeks/months/years to the date from a calendar - or ranges in regard to the keys?

[Or]

Your front-end objects don't impact your load script, so, this isn't what you're after, presumably.

[chriscammers]

There are really two viable options for this issue.

1. If your source data is not too huge then you can remove the condition for the transaction date and then use the transaction date as a filter in the dashboard so users can choose whatever dates they want to show in the sheets.
2. If your data is large then you could inject the values into the load script by building a dynamic view or ODAG application. In this case you would need to load the applicable range of dates into an application and provide flters where the user can choose the range of dates that they want. Then a "child" app is created that uses the selections from the first app as parameters in the load script.

It is not very common to use the ODAG approach as usually making selections in a normal applicaiton works very well for filtering data.