Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More
Not applicable

How to do the Incremental refresh in the fact Table

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.

4 Replies
adamdavi3s
Honored Contributor

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

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.

vinieme12
Esteemed Contributor II

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

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.

Not applicable

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

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.

vinieme12
Esteemed Contributor II

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

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;