topic Re: Increment load issue in QlikView

Increment load issue

Anonymous — Fri, 03 Mar 2017 10:21:03 GMT

Hi Community,

I am developing incremental load for one table. I have also attached my incremental load script file.

I am facing many issue in that.

1) In my Qvd store date field with timestamp and daily refreshing table also have date field with timestamp but when i using

both field for comparision purpose that time my script not working properly.

you can see in script when i try to fetch data from table Test for date 3/3/2017 and my QVD present data for 2/3/2017 and i only want data for date 3/3/2017 and write logic in query see below

Test:

Load

Field_Name ,

REPORTDATE // Date field

SQL SELECT

Field_Name ,

REPORTDATE

FROM Test

where (REPORTDATE)> $(vQVDDate)

but this logic not working

even this steps also not run

if $(vDBDate) > $(vQVDDate) then

So how i tackle this issue.

Thanks in advance.

Re: Increment load issue

khalander — Fri, 03 Mar 2017 10:28:02 GMT

Hi,

Try the below

where (REPORTDATE)> '$(vQVDDate)'

Re: Increment load issue

Anonymous — Fri, 03 Mar 2017 10:37:29 GMT

Hi Dada,

Thanks for you quick reply But I have written '$(vQVDDate)' in Single code i forgot to write in question.

But it not worked. any other solution. because REPORTDATE came from database and we are comparing in Oracle query. because i only want to fetch data for 3/3/2017 date. How i do this filtering at query level.

Re: Increment load issue

khalander — Fri, 03 Mar 2017 10:56:59 GMT

Hi,

Try to use date function

where date(REPORTDATE,'DD/MM/YYYY')> date('$(vQVDDate)','DD/MM/YYYY')

Re: Increment load issue

rahulpawarb — Fri, 03 Mar 2017 11:04:26 GMT

Hello Ishwar,

Could you please share the values of - vDBDate, vQVDDate and Test.REPORTDATE field along with it's format? Also, share the application with sample data, if possible. This will help us to find the root cause of the issue.

Regards!

Rahul

Re: Increment load issue

Anonymous — Fri, 03 Mar 2017 11:33:11 GMT

Hi Dada,

I have used date function in query but it is not working.

Re: Increment load issue

Anonymous — Fri, 03 Mar 2017 11:36:39 GMT

Hi Rahul,

I have shared Word file in that i have write sample code.

My vDBDate is - 3/3/2017 12:00:00 AM

My vQVDDate is 3/2/2017 12:00:00

My vDBDate is my Test.REPORTDATE i mention in code.

Re: Increment load issue

Anonymous — Fri, 03 Mar 2017 11:50:59 GMT

Hi Community,

Any solution of this question . because i want filter at query level.

How to apply filter at query.

is any function i can separate date from Timestamp

e.g.

I have date field in Oracle query like below

3/1/2017 12:00:00 AM

3/2/2017 12:00:00 AM

3/3/2017 12:00:00 AM

And

i want data like below

3/1/2017

3/2/2017

3/3/2017

which function i will used in Oracle query

Re: Increment load issue

Anil_Babu_Samineni — Fri, 03 Mar 2017 12:22:19 GMT

In fact, This question is related to Oracle. And you should move to Oracle forum

Anyhow

Qlikview is simple

Date(Date#(DateField,'DD/MM/YYYY hh:mm:ss tt'),'DD/MM/YYYY')

In Oracle, You may need this by help of To_Char

To_Char(DateField, 'dd/mm/yyyy')

Re: Increment load issue

shraddha_g — Fri, 03 Mar 2017 12:25:26 GMT

Try changing

Timestamp(max(REPORTDATE),'DD/MM/YYYY hh:mm:ss TT') AS QVD_MAX

and then reload

Re: Increment load issue

Anonymous — Fri, 03 Mar 2017 13:46:00 GMT

Hi Anil,

I have used this function it given value in string that when i debugging script this function showing value in ' ' single code and this not compare with QVD date. Qvd value in only date format.

is any other solution ?

Re: Increment load issue

Anil_Babu_Samineni — Fri, 03 Mar 2017 13:50:42 GMT

Haha, Can you confirm one thing? Are you trying this in Qlikview / Oracle ??

Re: Increment load issue

Anonymous — Fri, 03 Mar 2017 13:58:34 GMT

Hi Shradhha,

I have also used this function but when i compared this variable in Oracale Query it not work properly.

Is any other solution.

Re: Increment load issue

Anonymous — Fri, 03 Mar 2017 14:02:23 GMT

Hi Anil,

I have attached my incremental code file.

Please refer that file you will get idea what i am saying.

Re: Increment load issue

Anil_Babu_Samineni — Fri, 03 Mar 2017 14:36:00 GMT

From here, Where is Group By. I added can you do same.

Temp:

Load REPORTDATE,

DB_MAX

Sql Select REPORTDATE, DB_MAX

FROM Test;

Load REPORTDATE, DB_MAX, Max(REPORTDATE) as MaxRepDate Group By REPORTDATE

Resident Temp;

Re: Increment load issue

Anonymous — Mon, 06 Mar 2017 09:35:53 GMT

Hi Friends,

Thanks for your help.

I got answer to my question.

below link help me to resolve this issue.

1) https://community.qlik.com/thread/109953

From this thread i used below logic in my oracle query -

Test:

Load

field name,

Refresh date;

sql select

field name,

Refresh date

from Test where Refresh date > to_date('$(vQVDDate)', 'DD/MM/YYYY HH24:MI:SS');

Re: Increment load issue

Anil_Babu_Samineni — Mon, 06 Mar 2017 09:40:39 GMT

Good, When this is working with SQL. It should work in Qlikview by using below code help

Test:

Load

field name,

Refresh date from Test where Refresh date > Date('$(vQVDDate)', 'DD/MM/YYYY HH:MM:SS tt'); //"tt works as 12 hours" and "TT works as 24 hours in qlikview"