topic Incremental load in QlikView

Incremental load

Thu, 10 Sep 2009 11:05:14 GMT

Hi I am trying to do the incremental load with the following script.

Payrulefinal:

LOAD

Date,Month(Date) & Year(Date) as P_Monthyear,[Employee Category], [Employee Name, Salar, [TransportationAllowance],MVC, [Sales Commission], [Total PayoutRevenue], F10, [Gross Margin, [Overhead % to Revenue]

FROM

[..\XLS_DataBase\Payroll Overheads_new - Qlikview.xlsx]

(ooxml, embedded labels, table is Sheet1)

where

Month(Date) & Year(Date) = Month(today()) & Year(today());

concatenate

LOAD

Date,P_Monthyear, [Employee Category], [Employee Name], Salary, [Transportation Allowance], MVC, [Sales Commission], [Total Payout], Revenue, F10, [Gross Margin], [Overhead % to Revenue]

FROM

.. \QVD\Payrulefinal.qvd (qvd)

WHERE

NOT EXISTS(P_Monthyear);

store

Payrulefinal into

D:\QlikView\QVD\Payrulefinal.qvd;

What I am getting is only the latest data, for the pervious loaded data I am getting only single entry. Please help

Thanks

Jai

Incremental load

johnw — Thu, 10 Sep 2009 23:20:40 GMT

I suspect that the problem is the exists(P_Monthyear). As soon as you load ANY record from the QVD, that P_Monthyear will then exist in your table. In other words, it will only load one record for each P_Monthyear. Since you're only loading one record, I assume that all your records are for the same P_Monthyear.

One solution would be to load a duplicate field from the first source, like P_Monthyear_Temp. Then in the second load, use exists(P_Monthyear_Temp,P_Monthyear). Finally, drop P_Monthyear_Temp.

Incremental load

Thu, 10 Sep 2009 23:36:44 GMT

Load qvd :

LOAD Date,P_Monthyear, [Employee Category], [Employee Name], Salary, [Transportation Allowance], MVC, [Sales Commission], [Total Payout], Revenue, F10, [Gross Margin], [Overhead % to Revenue]

FROM .. \QVD\Payrulefinal.qvd (qvd)

WHERE P_Monthyear <> Month(today()) & Year(today());

LOOK http://community.qlik.com/media/p/61389.aspx

Suerte,

Incremental load

prieper — Thu, 10 Sep 2009 23:40:30 GMT

No - do not agree. This kind of syntay is correct. Would be worth for the OP to check, whether there are other data in the second qvd than in the first one ...

has crossed with another post

Peter