topic Re: Issue in incremental load in QlikView

Issue in incremental load

Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I am facing issue while performing incremental load. Data is not updating.

I am attaching the sample qvw/qvd file for reference and source input file.

Two excel sheet i have attached. One having sales data for 03/28 and other sheet which have data for 03/29.

This 03/29 sheet have some of the records need to be updated.

Please help me!!

Re: Issue in incremental load

aarkay29 — Wed, 29 Mar 2017 16:24:31 GMT

try this:

Last_update_date:

load max([Invoive Date]) as Maxdate

From

(qvd);

Let vLast_update_date = Date(peek('Maxdate',0,'Last_update_date'),'MM/DD/YYYY');

Drop Table Last_update_date;

Incremental:

LOAD [Sale Source],

[Transaction ID],

[Invoive Date],

[Customer Detail],

[Customer ID],

[Customer Area code],

[Bill No],

[Product Name],

[Transaction mode],

[Product quantity],

[Total sales amount]

FROM

[C:\Users\664393\Documents\Sales input_03292017.xlsx]

(ooxml, embedded labels, table is Sheet1) where [Invoive Date] > '$(vLast_update_date)';

Concatenate

LOAD [Sale Source],

[Transaction ID],

[Invoive Date],

[Customer Detail],

[Customer ID],

[Customer Area code],

[Bill No],

[Product Name],

[Transaction mode],

[Product quantity],

[Total sales amount]

FROM

[C:\Users\664393\Documents\SampleinputQVD.qvd]

(qvd)

where not Exists([Customer ID]);

STORE Incremental into [C:\Users\664393\Documents\SampleinputQVD.qvd]

(qvd);

DROP Table Incremental;

Re: Issue in incremental load

jonas_rezende — Wed, 29 Mar 2017 19:22:17 GMT

Hi, Rohit Kumar.

My suggestion is:

Create a key field to control the modification, because each key field is a fact. See example below of incremental load insert and update (no delete).

Last_update_date:

LOAD

Distinct

Max(Date([Invoive Date])) AS Maxdate

FROM

(qvd);

Let vLast_update_date = Date(peek('Maxdate',0,'Last_update_date'),'MM/DD/YYYY');

DROP Table Last_update_date;

SampleinputQVD:

LOAD

([Sale Source] & '_' & [Transaction ID] & '_ ' & [Customer ID] & '_' & [Customer Area code] & '_' & [Bill No] & '_' & [Product Name] & '_' & Date([Invoive Date]))

AS [Modification Data],

[Sale Source],

[Transaction ID],

Date([Invoive Date]) AS [Invoive Date],

[Customer Detail],

[Customer ID],

[Customer Area code],

[Bill No],

[Product Name],

[Transaction mode],

[Product quantity],

[Total sales amount]

FROM

(ooxml, embedded labels, table is Sheet1)

Where

Date([Invoive Date]) >= '$(vLast_update_date)';

Concatenate

LOAD [Sale Source],

[Transaction ID],

Date([Invoive Date]) AS [Invoive Date],

[Customer Detail],

[Customer ID],

[Customer Area code],

[Bill No],

[Product Name],

[Transaction mode],

[Product quantity],

[Total sales amount],

[Modification Data] //Already create in QVD.

FROM

(qvd)

where not Exists([Modification Data]);

STORE SampleinputQVD into

(qvd);

DROP Table SampleinputQVD;

P.S.: create a key field in QVD before run script. Utilize the attached that already exist (Modification Data field).

I hope this helps!

Jonas Melo.

Re: Issue in incremental load

Thu, 30 Mar 2017 07:08:25 GMT

Hi Aar,

Total 14 records needs to be there after successful insert and update. I have run the shared script but it is giving me only 10 records and respected date is also not updated. Please find the missing output file as below:

Customer Detail file for 0328 file	Output QVD
Suresh	Ramesh
Ramesh	Robin
Robin	Ronit
Rupesh	Subhash
Saurav	Vardhan
Vishal	Suresh
Vikas	Vikas
Naveen	Naveen
Vivek	Vivek
Navneet	Navneet
Customer Detail file for 0329 file	Not in the output QVD
Bipin	Bipin
Ramesh	Rupesh
Robin	Vishal
Ronit	Saurav
Subhash
Vardhan

Thanks,

Rohit

Re: Issue in incremental load

Thu, 30 Mar 2017 07:14:34 GMT

Hi Jonas,

I am expecting total 14 record in output QVD but the resulted output is giving 10 records. Please see also my reply to Aar for details.

Thanks,

Rohit

Re: Issue in incremental load

aarkay29 — Thu, 30 Mar 2017 14:01:37 GMT

Hi Rohit,

According to your requirement BillNo and Customer Detail seem to be the compositekey

Let me know if that is incorrrect.

PFA

Re: Issue in incremental load

jonas_rezende — Thu, 30 Mar 2017 14:57:10 GMT

Hi, Rohit Kumar.

Please, list the 4 registers new and do upload of data source.

Regards,

Jonas Melo.