topic Re: Fact incremental load in QlikView

Fact incremental load

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi Experts,

Can anyone of you explain me about fact table incremental load. I have an idea about for dimension.

But confusion in fact incremental load.

Thanks in advance

Regards,

Kumar

Re: Fact incremental load

rahulpawarb — Mon, 17 Apr 2017 10:21:16 GMT

Hello Kumar,

Please refer attached pdf file. Please let me know if you have any questions.

Regards!

Rahul

Re: Fact incremental load

Anonymous — Mon, 17 Apr 2017 10:28:25 GMT

Hi Rahul,

Thanks for your attachment. But in this document it contains only Inserts, updates and delete scripts.

But in my fact table it will have only inserts on daily basis around 10k records.

Can you help me on this only inserts technique.

Regards,

Kumar

Re: Fact incremental load

rahulpawarb — Mon, 17 Apr 2017 11:05:52 GMT

Hello Kumar,

Thank you for the prompt response.

Please refer below sample script for incremental load (INSERT ONLY):

//Set lower and upper bound values using variables
LET vExecTime = UTC();
LET vLastExecTime = 0;


//Get the upper bound value and set it into variable
LoadTime:
LOAD MaxString(LastModifiedDate) AS LastModifiedDate
FROM Table1.qvd(qvd);


LET vLastExecTime = Peek('LastModifiedDate', 0, 'LoadTime');


//Load the history data from QVD file
Table1:
LOAD Id,
...
FROM Table1.qvd(qvd);


//Load the changed data from base table and append it to result of QVD file load 
Concatenate(Table1)


Incremental:
LOAD Id,
...
FROM <TableName>
WHERE LastModifiedDate >= $(vLastExecTime)
AND   LastModifiedDate < $(vExecTime);

If you still have further queries then please share application file.

Hope this will be helpful.

Regards!

Rahul

Re: Fact incremental load

Anonymous — Mon, 17 Apr 2017 12:00:03 GMT

Hi Rahul,

Once again Thanks,

I have used script as mentioned above.

As of now, I don't have any qvd in related to my fact. Since it is searching for fact and is failing.

Please see that screen shot.

Regards,

Kumar

Re: Fact incremental load

Anonymous — Mon, 17 Apr 2017 14:12:57 GMT

I am trying to do with following script.

But problem here is

LET BeginningThisExecTime = Now();

LET LastExecTime = BeginningThisExecTime ;

IncrementalExtraction:

SQL SELECT PrimaryKey, Field1, Field2 FROM Table_X

WHERE ModificationTime >= #$(LastExecTime)#

AND ModificationTime < #$(BeginningThisExecTime)#;
HistoryData:

LOAD *

RESIDENT IncrementalExtraction;

Concatenate

LOAD *

FROM $(SourceFilePath)History.QVD

WHERE NOT EXISTS (PrimaryKey,PrimaryKey);

STORE HistoryData INTO $(SourceFilePath)History.qvd;

In above script variable LET BeginningThisExecTime = Now(); shouldn't use.

why because in fact dates will never match with NOW().

If use NOW() variable related will never match in below highlighted.

Here in below script variable($(LastExecTime)) always should come from my fact table date only

IncrementalExtraction:

SQL SELECT PrimaryKey, Field1, Field2 FROM Table_X

WHERE ModificationTime >= #$(LastExecTime)#

AND ModificationTime < #$(BeginningThisExecTime)#;

Pls help me how to use that corresponding dates form my fact table

Regards,

Kumar

Re: Fact incremental load

rahulpawarb — Tue, 18 Apr 2017 03:54:15 GMT

Hello Kumar,

Could you please share some sample data using which you are trying to implement incremental load?

Regards!

Rahul