topic Re: Incremental Load without date? in QlikView

Incremental Load without date?

Thu, 22 Nov 2012 10:09:30 GMT

Hi I have primary keys in a table:

LOAD

`product_sk`,

    `merchant_sk`,

    `brand_name`,

    `product_id`,

    `product_name`,

    `redemption_type`,

    `product_type`,

    `category_id`,

    `category_name`,

    `release_date`,

    `currency`,

    `net_retail_price`,

    discount,

    `vat_cost`,

    `is_active`,

    `article_id`,

    `product_description`,

    ValueDocumentType;

SQL SELECT *

Here product_sk is primary key can any one please suggest a incremental load using primary key? Please note that in several huge tables we do not have date dimension. I am looking forward for a crashproof script that should not use temp date like Let today's date = UTC, because many days data job failed then I always required to load full table again.

Can you please help me with some script where it will check max product_sk in QVD and tally it with My SQL, then load?

Many Thanks

- Deep

Re: Incremental Load without date?

Thu, 22 Nov 2012 10:37:00 GMT

Maybe this might be of help

QV_Table:
SQL SELECT Primary Key,X,Y From DB_Table
Where ModificationTime>=#$(LastExecTime)#;

Concatenate LOAD Primary Key,X,Y From File.QVD

WHERE NOT Exists (PrimaryKey);

STORE QV_Table INTO File.QVD;

Regards

Mhatim

Re: Incremental Load without date?

jagannalla — Thu, 22 Nov 2012 10:46:21 GMT

Hello,

Here is the code:

OldQVD:
LOAD
     Product_sk
FROM
Main.qvd (qvd);
MaxOldQVD:
LOAD
Max(Product_sk) as MaxProduct_sk
Resident OldQVD;
LET vMaxProduct_sk = Peek('MaxProduct_sk',-1,MaxOldQVD);
DROP Table OldQVD;
DROP Table MaxOldQVD;
Main:
LOAD *
FROM
Main.qvd;

Concatenate
Load * ;
Select * from dbo.Main
where Product_sk > $(vMaxProduct_sk);
STORE Main into Main.qvd;

Hope it helps you..

Cheers!!

Jagan

Re: Incremental Load without date?

Fri, 23 Nov 2012 11:53:21 GMT

That Helps But I wanted with no time stamp.

Re: Incremental Load without date?

Fri, 23 Nov 2012 11:57:03 GMT

It works perfect! Thank You for suggession, I made some changes so that even for 1st time if it do not find main qvd, it will generate. OR It will fetch data from main. Here it is:

Let vQVDPath='C:\Users\...\Main.qvd';

if not IsNull(QvdCreateTime('$(vQVDPath)')) then

OldQVD:

LOAD

product_sk

FROM
(qvd);

MaxOldQVD:

LOAD

Max(product_sk) as max_product_sk

Resident OldQVD;

LET vMaxProduct_sk = Peek('max_product_sk',-1,MaxOldQVD);

DROP Table OldQVD;

DROP Table MaxOldQVD;

Main:

LOAD *

FROM

(qvd);

Concatenate

Load * ;

Select * FROM `ll_etl`.`test_qv_load`

where product_sk > $(vMaxProduct_sk);

STORE Main into ;

ENDIF

// full query

if IsNull(QvdCreateTime('$(vQVDPath)')) then

Main:

Select * FROM `ll_etl`.`test_qv_load`;

STORE Main into ;

ENDIF

Hope it will help others!!! Thank You!