topic Re: INCREMENTAL LOAD HELP PLS in QlikView

INCREMENTAL LOAD HELP PLS

Tue, 25 Aug 2015 16:10:48 GMT

HI All,

I want to know how many new lines are fetched after incremental load?

i.e.,

In my qvd ,i have 500 files

after 1st incremental load, total 600 lines are fetched........

i.e., 100 records are new ones..

IS THERE any process to get how many new records are fetched to see in dashboard by using variable or something...........

Re: INCREMENTAL LOAD HELP PLS

kaushiknsolanki — Tue, 25 Aug 2015 17:05:36 GMT

Hi,

You can create an intermediate table (QVD) in incremental script, which stores the count of new records on day to day basis.

Regards,

Kaushik Solanki

Re: INCREMENTAL LOAD HELP PLS

Anonymous — Tue, 25 Aug 2015 17:10:53 GMT

hi ramya,

From QlikView Help

Trace

The trace statement writes a string to the Script Execution Progress window and to the script log file, when used.

It is very useful for debugging purposes. Using $-expansions of variables that are calculated prior to the Tracestatement, you can customize the message.

The syntax is:

trace string

Examples:

trace Main table loaded;

Let MyMessage = NoOfRows('MainTable') & ' rows in Main Table';

trace $(MyMessage);

Re: INCREMENTAL LOAD HELP PLS

Qrishna — Tue, 25 Aug 2015 17:12:29 GMT

one trick:

put the expressions:

LET vQVDRecordCounter= NoOfRows('FileName') at the end of script;

and you can call the variable on UI.

Re: INCREMENTAL LOAD HELP PLS

Wed, 26 Aug 2015 02:44:33 GMT

I didnt get you,

please elaborate & where i have 2 create intermediate script

{//This is my script

Incremental:

LOAD

Id,

Sales,

Modified_Date

FROM

(ooxml, embedded labels, table is Sheet1)where Modified_Date>$(Last_Updated_Date);

Concatenate

LOAD

Id,

Sales,

Modified_Date

FROM

(qvd)Where not Exists (Id);

Inner Join

LOAD Id

FROM

(ooxml, embedded labels, table is Sheet1);

STORE Incremental into Sales.qvd;

DROP Table Incremental;

Pinky:

LOAD Id,

Sales,

Modified_Date

FROM

(qvd);

Re: INCREMENTAL LOAD HELP PLS

jagan — Wed, 26 Aug 2015 03:06:30 GMT

Hi Ramya,

You can try like below to store the new records count in variable

Incremental:

LOAD

Id,

Sales,

Modified_Date

FROM

(ooxml, embedded labels, table is Sheet1)where Modified_Date>$(Last_Updated_Date);

LET vOldRecordsCount = NoOfRows('Incremental');

Concatenate

LOAD

Id,

Sales,

Modified_Date

FROM

(qvd)Where not Exists (Id);

Inner Join

LOAD Id

FROM

(ooxml, embedded labels, table is Sheet1);

STORE Incremental into Sales.qvd;

LET vTotalRecordsCount = NoOfRows('Incremental');

LET vNewRecordsCount = vTotalRecordsCount - vOldRecordsCount ;

DROP Table Incremental;

Pinky:

LOAD Id,

Sales,

Modified_Date

FROM

(qvd);

You will get the new records count in vNewRecordsCount variable after execution of the script;

Hope this helps you.

Regards,

Jagan.

Re: INCREMENTAL LOAD HELP PLS

Wed, 26 Aug 2015 03:16:50 GMT

Hi jagan,

Im getting OldRecords Count..

After Incremental table

u have written LET vOldRecordsCount = NoOfRows('Incremental');

This is new records count

Re: INCREMENTAL LOAD HELP PLS

jagan — Wed, 26 Aug 2015 03:57:04 GMT

Hi Ramya,

Try this

Incremental:

LOAD

Id,

Sales,

Modified_Date

FROM

(ooxml, embedded labels, table is Sheet1)where Modified_Date>$(Last_Updated_Date);

LET vNewRecordsCount = NoOfRows('Incremental');

Concatenate

LOAD

Id,

Sales,

Modified_Date

FROM

(qvd)Where not Exists (Id);

LET vRecordsInQVD = NoOfRows('Incremental') - vNewRecordsCount ;

Inner Join

LOAD Id

FROM

(ooxml, embedded labels, table is Sheet1);

STORE Incremental into Sales.qvd;

LET vFinalTotalRecordsCount = NoOfRows('Incremental');

DROP Table Incremental;

Pinky:

LOAD Id,

Sales,

Modified_Date

FROM

(qvd);

Hope this helps you.

Regards,

jagan.