topic Convert SQL date query to QlickVIEW load in QlikView

Convert SQL date query to QlickVIEW load

sculptorlv — Wed, 25 Nov 2020 16:16:04 GMT

Hello and please help!

I use the following SQL statement during data first load:

TABLE_DATA:

TABLE_SALES_INVOICE_LINES."Posting Date" >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 2, 0)

AND

TABLE_SALES_INVOICE_LINES."Posting Date" <=DATEADD(DAY, -(DAY(GETDATE())), GETDATE())

In such way, I got all sales data for full previous TWO month.

Then, I need to do some additional load in qlickviev .. I use

LOAD

...

RESIDENT TABLE TABLE_DATA

WHERE ..

XXXXXXXXXXX

XXXXXX - I need to convert the same query to qlikview syntax in order to get data only for 1 month period. But SQL syntax doesn't work anymore.

YoussefBelloum — Thu, 08 Feb 2018 10:42:00 GMT

Hi,

if your sql query (the first one which limits the data on the last 2 months) is working correctly,

so you can try to modify this query to just load only the last month (if it is the only thing you need to modify).

if you need to do another transformations, you can make this:

final_table:

load

tranformations...

;

sql select

your_sql_query;

like this qlikview will read first your sql query first, and use the result of this query as source for the first part (which is Qlik load part).

is it clear ?

sculptorlv — Thu, 08 Feb 2018 10:45:20 GMT

Thank you for help!

I found an answer in my previous topic (asking the same for the same... sorry).

I needed this qurey:

Sales_Posting_Date >= MonthName(MonthStart(Today())-1)

AND

Sales_Posting_Date < MonthStart(Today())