topic Incremental load - scripting problems in QlikView

Incremental load - scripting problems

Fri, 08 Jun 2012 06:36:33 GMT

Hi!

I have som problem writing my script that should be used for incremental load of data. As always when using incremental load I just want to load the posts that is new by looking at the field called CREATE_TIMESTAMP.

The problem is that i have to tables that i want to perform incremental load on, lets call it table1 and table2, in an Oracle database. It works fine when i am loading only from table1, only the new posts is loaded in to my qvd-file each time. When this is done i just want to load the data in table2 that is connected to the loaded posts in table1 since the other data already should be loaded. Here's the catch, the CREATE_TIMESTAMP is only in table1 and table2 is associated with table1 with an key (table1.ID = table2.ID2). I ONLY want to load the posts from table2 that is associated via the key on the loaded posts in table1.

Hope you understand my problem.

Thank you.

Hannes

Incremental load - scripting problems

shantanu73 — Fri, 08 Jun 2012 06:45:39 GMT

Hello Hannesse,

I am not so very clear. If u provide me the script I may understand. And then I may provide you the solution.

Thank

Shantanu

Incremental load - scripting problems

Fri, 08 Jun 2012 07:39:53 GMT

shantanu73, heres the script:

/* Setup for incremental load. */

SET vQvdFile='File.qvd';

SET vPKID='ID1';

// Set a variable indicating if the QVD exists or not. -1 is True, 0 is False

LET vQvdExists = if(FileSize('$(vQvdFile)') > 0, -1, 0);

IF $(vQvdExists) THEN // QVD exists, we will do an incremental reload

maxdateTab:

// Get the max date from this QVD for use in incremental SELECT

LOAD max(CREATE_TIMESTAMP) as maxdate

FROM $(vQvdFile) (qvd);

LET vIncrementalExpression = 'WHERE CREATE_TIMESTAMP >= ' & chr(39) & timestamp(peek('maxdate')) & chr(39);

DROP table maxdateTab;

ELSE // QVD does not exist

LET vIncrementalExpression = ''; // No QVD. Force full reload

END IF

ODBC CONNECT TO [database;DBQ=DATABASE ] (XUserId is XXXXX, XPassword is YYYY);

Table1:

SQL SELECT BIRTHDATE,

"FIRST_NAME",

GENDER,

"CREATE_TIMESTAMP",

ID as "ID1", //Primary Key

"LAST_NAME",

"WORK_TITLE",

FROM PROD.TABLE1;

//Heres the problem, i only want to load the posts that is associated with the newly loaded posts from table1

//Maybe i need to store old posts from table2 in another qvd-file as well and concatenate the new posts to that?

Table2:

SQL SELECT "DATE",

"RESULT",

ID2,

ID1 as "Table2_ID" //NOTE THAT ID2 is the field that should be associated with ID1 in table1

FROM PROD.TABLE2;

Update the QVD with changes.

Directory;

IF $(vQvdExists) THEN

// Use CONCATENATE in case we've added any new fields.

CONCATENATE (Table1) LOAD * FROM $(vQvdFile) (qvd)

WHERE NOT exists($(vPKID)) // Load only QVD rows that were not already loaded in the data load.

;

END IF

//Overwrite the QVD with the QV datatable.

STORE Table1 INTO $(vQvdFile);

THANKS!

/Hannes

Incremental load - scripting problems

shantanu73 — Mon, 11 Jun 2012 05:08:09 GMT

Hello Hannes,

Sorry of Late Answer,

Check the following Script. If you get some idea.

//Start from Here:

Let ThisExecTime = ReloadTime();

QV_Table:
SQL SELECT PrimaryKey, X, Y FROM DB_TABLE
WHERE ModificationTime >= #$(LastExecTime)#
AND ModificationTime < #$(ThisExecTime)#;

Concatenate LOAD PrimaryKey, X, Y FROM File.QVD
WHERE NOT EXISTS(PrimaryKey);

Inner Join SQL SELECT PrimaryKey FROM DB_TABLE;

If ScriptErrorCount = 0 then
STORE QV_Table INTO File.QVD;
Let LastExecTime = ThisExecTime;
End If

//End from Here:

Regards

Shantanu

Incremental load - scripting problems

Mon, 11 Jun 2012 07:25:14 GMT

Thank you Shantanu, after some modification it works fine!

Aanother problem: I want to count how many times the PrimaryKey occurs in the table. Problem is that one post in table1 can be connected to several posts in table2, but still, i only want to count it once. If I do count(PrimaryKey) it returns to many!

Thank you in advance!

Hannes

Incremental load - scripting problems

Mon, 11 Jun 2012 07:50:22 GMT

Hi Hannes,

Maybe you can do this.

table1:

Load *,

PrimaryKey as pKey

From xxx

Then count the number of pKey in table 1 should give you the correct count.

Regards,

Xue Bin

Incremental load - scripting problems

Mon, 11 Jun 2012 07:56:02 GMT

You Chek this One.

Table_Name:

LOAD *;

SQL SELECT * FROM Table_Name

where TO_CHAR(to_DATE($(vModificationTime),'YYYY/MM/DD HH24:MI:SS'),'YYYYMMDDHH24MISS')>='$(T)';

Concatenate ([Table_Name])

Table_Name:

LOAD * from Table_Name .qvd (qvd)

WHERE NOT Exists ($(Y));

inner join ([Table_Name])

sql select Pk FROM Table_Name;

ENDIF

store Table_Name into Table_Name.qvd ;

//..............................................................................................//

LET T= (DATE(NOW(),'YYYYMMDD') & ((hour(NOW()))*10000) + (MINUTE(NOW())*100) + SECOND(NOW()));

Incremental load - scripting problems

Mon, 11 Jun 2012 08:56:01 GMT

adityatiwari, that looks pretty much the same as my script. It still returns to many of PrimaryKey...

Hannes

Incremental load - scripting problems

Mon, 11 Jun 2012 09:46:30 GMT

select TABLE_NAME from SYS.ALL_TABLES

WHERE TABLESPACE_NAME=USERS AND OWNER=OWNER_NAME;

Left Join

SELECT A.TABLE_NAME , B.COLUMN_NAME

from all_constraints A ,

all_cons_columns B

WHERE A.CONSTRAINT_TYPE ='P' AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME

ORDER BY A.TABLE_NAME;

This quarry Return Pk for all table and that pk is used in script.

Incremental load - scripting problems

Mon, 11 Jun 2012 14:07:36 GMT

Thank you, I think i solved it!

/Hannes