topic Re: How to do the Incremental refresh in the fact Table in QlikView

How to do the Incremental refresh in the fact Table

Tue, 11 Oct 2016 09:13:48 GMT

Hi Experts,

I can do the Incremental refresh for the dimension table using the Primary Key. See below Script

Last_Updated_Date:

Load Max([Created Date]) as Date

FROM

(qvd);

Let Last_Modified_Date=Peek('Date', 0,'Last_Updated_Date');

Customer:

LOAD Name,

CustomerID,

[Created Date]

FROM

(ooxml, embedded labels, table is Customer) where [Created Date]>$(Last_Modified_Date);

Concatenate

LOAD Name,

CustomerID,

[Created Date]

FROM

(qvd) where not Exists (CustomerID);

But the Question is How to do the Incremental refresh for the fact table since fact table contains only foreign key.????

Thanks In advance .

Regards,

Kabilan K.

Re: How to do the Incremental refresh in the fact Table

adamdavi3s — Tue, 11 Oct 2016 11:53:17 GMT

Hi Kabilan,

I am not sure what keys you are talking about exactly... why is this having an impact on reloading your fact table? Do you not simply have a date field you can test in the same way as you have done your customers?

Do you mean that the key on the fact table is not unique? If so then simply create yourself a key to use for the load.

Re: How to do the Incremental refresh in the fact Table

vinieme12 — Tue, 11 Oct 2016 12:46:44 GMT

Have you tried yet?

Can you post the FACT script which is in question?

You only need a key field that uniquely identifies each row of data that is all you need to implement an incremental load.

Re: How to do the Incremental refresh in the fact Table

Tue, 11 Oct 2016 12:58:24 GMT

Yeah Yes Fact Table will not have the unique value, Here I have attached my sample data.

Script for sales:

Last_Updated_Date_Sales:

Load Max(InvoiceDate) as Date

FROM

(qvd);

Let Last_Modified_Date_Sales=Peek('Date', 0,'Last_Updated_Date_Sales');

Sales:

LOAD CustomerID,

ProductID,

Qty,

UnitPrice,

InvoiceDate

FROM

(ooxml, embedded labels, table is Sales) where InvoiceDate>$(Last_Modified_Date_Sales);

Concatenate

LOAD CustomerID,

ProductID,

Qty,

UnitPrice,

InvoiceDate

FROM

(qvd) where not Exists(CustomerID&'#'&ProductID);

Store Sales into "E:\Qvd\Create\Sales.qvd";

Drop table Sales;

This will not work as we expected in dimension table Customer and Product.

Thanks In Advance

Regards,

Kabilan K.

Re: How to do the Incremental refresh in the fact Table

vinieme12 — Tue, 11 Oct 2016 13:04:53 GMT

What you put within bracket of the exist () function must exist in both tables

Last_Updated_Date_Sales:
Load Max(InvoiceDate) as Date
FROM

(qvd);

Let Last_Modified_Date_Sales=Peek('Date', 0,'Last_Updated_Date_Sales');

Sales:
LOAD CustomerID,
ProductID,
Qty,
UnitPrice,
InvoiceDate,
CustomerID&'#'&ProductID&'#'&InvoiceDate as linekey   //Keyfield to check

FROM

(ooxml, embedded labels, table is Sales) where InvoiceDate>$(Last_Modified_Date_Sales);

Concatenate

LOAD CustomerID,
ProductID,
Qty,
UnitPrice,
InvoiceDate,
CustomerID&'#'&ProductID&'#'&InvoiceDate as linekey     //Keyfield to check

FROM

(qvd) where not Exists(CustomerID&'#'&ProductID&'#'&InvoiceDate);     //Keyfield to check must exist in both table

Store Sales into "E:\Qvd\Create\Sales.qvd";

Drop table Sales;