topic Re: New Data in QlikView

New Data

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

HI All,

Can we get New data without Incremental load.

like use last execution date..

I have million's of records.. How can we reload only new data?????

Let vLastReload= ReloadTime();

Load CategoryID,

CategoryName,

Price,

Model

From Category;

Can we use variable in where Condition ??

Thanks in Advance...

Re: New Data

Gysbert_Wassenaar — Wed, 23 Mar 2016 06:50:10 GMT

You can use a technique that's called incremental loading. See here, here and here for more information.

And to make things a lot easier to implement read Rob's blog post on incremental loading using qlikview components.

Re: New Data

Kushal_Chawda — Wed, 23 Mar 2016 06:50:16 GMT

see this

http://www.analyticsvidhya.com/blog/2014/09/qlikview-incremental-load/

Re: New Data

Chanty4u — Wed, 23 Mar 2016 06:52:27 GMT

did u try with partial reload?

by using

Add

Re: New Data

jagan — Wed, 23 Mar 2016 07:04:41 GMT

Hi,

You have to load the new data with WHERE condition, but if you require old data in Dashboard then you have save the old data in QVD (for faster reloading) and then need to concatenate to the new data everytime.

Regards,

Jagan.

Re: New Data

Nagaraju_KCS — Wed, 23 Mar 2016 07:34:29 GMT

HI All,

Please anyone can help me.. I am Confusing..

First time implementing Incremental Load..

This is my table..

TestTable:

Load

Category ID,

Category,

Product,

DateCompleted,

Price

from Category;

How to do Incremental load, I have date field DateCompleted.

Only for Insert

Thanks in Advance...

Re: New Data

jagan — Wed, 23 Mar 2016 07:46:29 GMT

Hi,

First understand what is incremental load and how it works. Check Qlikview help file there is very good explanations there with sample script.

Also go through the links shared by gwassenaar‌ and Kush141087‌ to understand.

The purpose of incremental load is to reduce the load on databases by reading only the latest records.

LET vToday = Date(Floor(LastReloadTime()));

LET vQVDPath = 'C:\QVD\';

// If QVD is found then reload after last reload time if not reload from Jan-2010 (Adjust this based on your requirement)

IF Alt(FileSize('$(vQVDPath)' & '\Data.qvd'), 0) > 0 THEN

LET vLastReloadData = Date(Floor(ReloadTime()), 'MM/DD/YYYY');

ELSE

LET vLastReloadData = Date(MakeDate(2010, 1), 'MM/DD/YYYY');

END IF

Example:

SELECT

FROM TableName

WHERE DateFieldName >= TO_DATE('$(vLastReloadData)', 'MM/DD/YYYY');

// Load Past data from QVD file

IF Alt(FileSize('$(vQVDPath)' & '\Data.qvd'), 0) > 0 THEN

Concatenate(Actual)

LOAD *

FROM [$(vQVDPath)\Data.qvd] (qvd)

WHERE DateFieldName < '$(vLastReloadTimeActual)';

END IF

STORE Data INTO [$(vQVDPath)\Data.qvd];

DROP TABLE Data;

//Now your QVD has all old and new data, you can use this qvd anywhere you required.

Regards,

Jagan.

Re: New Data

Nagaraju_KCS — Wed, 23 Mar 2016 08:35:56 GMT

Thanks Jagan..

I am Fetching from SQL Data,

I have implemented as above code..

After script execution i am getting failure.

Any idea??

Re: New Data

Gysbert_Wassenaar — Wed, 23 Mar 2016 08:42:02 GMT

Posting the error might be an idea. Most of can't read minds.

Re: New Data

Nagaraju_KCS — Wed, 23 Mar 2016 08:45:44 GMT

HI,

Is this Correct ??

Data i am fetching from SQL....

LET vToday = Date(Floor(ReloadTime()));

LET vQVDPath = 'D:\QVD\';

IF Alt(FileSize('$(vQVDPath)' & '\Data.qvd'), 0) > 0 THEN

LET vLastReloadData = Date(Floor(ReloadTime()), 'MM/DD/YYYY');

ELSE

LET vLastReloadData = Date(MakeDate(2014, 1), 'MM/DD/YYYY');

END IF

Table:

SELECT

Category ID,

Category,

Product,

DateCompleted,

Price

FROM Category

WHERE DateCompleted>= TO_DATE('$(vLastReloadData)', 'MM/DD/YYYY');

IF Alt(FileSize('$(vQVDPath)' & '\Data.qvd'), 0) > 0 THEN

Concatenate(Table)

LOAD *

FROM [$(vQVDPath)\Data.qvd] (qvd) Note: 1st time i don't have this QVD

WHERE DateCompleted< '$(vLastReloadData)';

END IF

STORE Data INTO [$(vQVDPath)\Data.qvd];

DROP TABLE Table;

Thanks..

Re: New Data

Nagaraju_KCS — Wed, 23 Mar 2016 08:47:16 GMT

I am getting this error. and Application closing

Execution of Script failed. Reload old data??

Re: New Data

Kushal_Chawda — Wed, 23 Mar 2016 09:02:08 GMT

Make sure that QVD path is correct

LET vToday = Date(Floor(ReloadTime()));

LET vQVDPath = 'D:\QVD\';

IF Alt(FileSize('$(vQVDPath)' & 'Data.qvd'), 0) > 0 THEN

LET vLastReloadData = Date(Floor(ReloadTime()), 'MM/DD/YYYY');

ELSE

LET vLastReloadData = Date(MakeDate(2014, 1), 'MM/DD/YYYY');

END IF

Table:

SELECT

Category ID,

Category,

Product,

DateCompleted,

Price

FROM Category

WHERE DateCompleted>= TO_DATE('$(vLastReloadData)', 'MM/DD/YYYY');

IF Alt(FileSize('$(vQVDPath)' & 'Data.qvd'), 0) > 0 THEN

Concatenate(Table)

LOAD *

FROM [$(vQVDPath)Data.qvd] (qvd) Note: 1st time i don't have this QVD

WHERE DateCompleted< '$(vLastReloadData)';

END IF

STORE Data INTO [$(vQVDPath)Data.qvd];

DROP TABLE Table;

Re: New Data

jagan — Wed, 23 Mar 2016 09:08:02 GMT

Hi,

Do you have this path? D:\QVD\

Regards,

Jagan.

Re: New Data

Nagaraju_KCS — Wed, 23 Mar 2016 09:27:53 GMT

I got it..

QVD path it should be Double Quote's

Re: New Data

Nagaraju_KCS — Mon, 28 Mar 2016 13:47:12 GMT

Hi All,

I have Developed Incremental load, It's working fine..

I am Fetching from SQL Data....

But Full reload also taking same time..... Then what is the use of Incremental Load??????

Should I do anything change code??

Please give me suggestions.................

SET vQvdFile='Test.qvd';

SET vTableName='myTable';

LET vQvdExists = if(FileSize('$(vQvdFile)') > 0, -1, 0);

IF $(vQvdExists) THEN

maxdateTab:

LOAD max(DateField) as maxdate

FROM $(vQvdFile) (qvd);

LET vIncrementalExpression = 'WHERE DateField>=' & peek('maxdate');

DROP table maxdateTab;

ELSE

LET vIncrementalExpression = '';

END IF

$(vTableName):
LOAD
A,
DateField,
C SQL SELECT *
FROM DB.dbo."Table"
$(vIncrementalExpression);

IF $(vQvdExists) THEN

CONCATENATE ($(vTableName))

LOAD * FROM $(vQvdFile) (qvd);

End IF

STORE $(vTableName) INTO Test.qvd(qvd);

EXIT Script

If we have taking same time Incremental load and Full reload...

Then what is the use of Incremental Load....

Should i do any Optimization??

Thanks in Advance..

Re: New Data

Nagaraju_KCS — Tue, 29 Mar 2016 04:57:41 GMT

HI All,

I am fetching data from SQL. If i am reload full data, it's taking 12 mins.

I have implemented Incremental load for new records.

It's talking more than 50 mins.

Did i anything wrong??

Please let me know...

Thanks...

Re: New Data

settu_periasamy — Tue, 29 Mar 2016 09:17:59 GMT

Hi,

Can you just check this part only? and check the duration..

$(vTableName):
LOAD
A,
DateField,
C SQL SELECT *
FROM DB.dbo."Table"
$(vIncrementalExpression); //Manually give your condition

Re: New Data

Nagaraju_KCS — Tue, 29 Mar 2016 11:32:49 GMT

Hi Settu,

If i am give normal condition, it's reloading whole data.

Please help me out..

Re: New Data

settu_periasamy — Tue, 29 Mar 2016 11:40:12 GMT

No, just wanted to check which part is taking much time.. Instead of using the variable (fetch data from SQL),

try to give the specific date..

is it possible to provide Log file?

Test:
LOAD
A,
DateField,
C;

SQL SELECT *

FROM DB.dbo."Table"
WHERE DateField>='1/1/2016';

Re: New Data

Nagaraju_KCS — Tue, 29 Mar 2016 11:57:16 GMT

This is working fine for Excel Data.

My SQL data date field format (like 23/04/2015 00:00:00)

is anything problem here??

Maxdate:

Load date(Max("Date"),'YYYY-MM-DD') as Maxdate

from Tablename;

LET vIncrementalExpression = 'WHERE DateField>=' & peek('maxdate');

Here i have converted Date YYYY-MM-DD, but SQL data DD/MM/YYYY

Maybe compare issue.. if i am use that variable complete hang (lots of time).