Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
Not applicable

Reload data

Hello everyone, I'm new to qlikview and I have searched for information regarding the following but I have not found , so I decided to post it here :

Turns out I loaded into a database via ODBC interface to qlikview .

And all right, I managed to do it , I used the script looks like this :

ODBC Connect32 TO BDFricoTemuco ( XUserId is XXXLLLYYY , XPassword is XXXLLLYYY ) ;

Select * from clickvtemuco ;

And here's the first problem, the database is connected, but when I do select statement Select by creating shows no . What compels me to manipulate the fields earlier in EMS before loading to qlikview .

But my problem is not that , because that suplo it with programming in EMS.

My problem is that when I want to refresh my updated database qulikview data , duplicates are generated .

ODBC Connect32 TO BDFricoTemuco ( XUserId is XXXLLLYYY , XPassword is XXXLLLYYY ) ;

Select * from clickvtemuco add ;

ODBC Connect32 TO BDFricoTemuco ( XUserId is XXXLLLYYY , XPassword is XXXLLLYYY ) ;

Select * from clickvtemuco replace ;

I used separate ADD and REPLACE command, but I do not know if I used the wrong script or do not know but it did not work as I wanted, what I did was re-add all the data even when they were generating before redundancy.

And what I needed was to update the data adding only new data from the database

Please help

from already thank you very much

David

( excuse my English , but it helped me GoogleTranslate )

Tags (4)
1 Solution

Accepted Solutions

Re: Reload data

Some pointers to more information:

QlikView Reference Manual: Chapter 28.4 Using QVD Files for Incremental Load

Community: Re: Dynamic Update and Incremental Loading Gysbert provides a few useful links to discussions about Incremental Loading

Document: Incremental Load Strategies in this discussion: Re: QVD Files

Good luck,

Peter

View solution in original post

7 Replies

Re: Reload data

You do not have to ADD or REPLACE (especially not in SQL). Every script execution starts with a general wipe of all the existing data. At the start of the script, you'll be left with ... nothing.

A script will then again reload your source tables into fresh QlikView tables. No duplicates...

Loading only new additions to the source tables into your existing QlikView document is called "Incremental Loading". There is a lot of information available in the community, because this is a very flexible technique that is highly dependent on your source data. For instance, how can you know which records are new? Do you have DateTime or TimeStamp fields in your source tables?

Peter

Re: Reload data

You do not need to use Add or Replace, just use the first script you listed

     ODBC Connect32 TO BDFricoTemuco ( XUserId is XXXLLLYYY , XPassword is XXXLLLYYY ) ;

      Select * from clickvtemuco ;

A better script would allow you to manipulate the fields in QlikView

      ODBC Connect32 TO BDFricoTemuco ( XUserId is XXXLLLYYY , XPassword is XXXLLLYYY ) ;

      YourTableName:

     load

          YourFieldName1,         

          YourFieldName2,        

           YourFieldNameX ;

     SQL Select * from clickvtemuco ;

You will have to change the field names to match your data.

QlikView can generate this script for you if you select "Preceding Load" in the script options

load.JPG.jpg

Not applicable

Re: Reload data

First of all , thanks for responding and trying to help me.

I loaded the ODBC database , and everything was connected ... I could even visualize the data in the view (as seen at the bottom of the image) , but I fail to manipulate them from "creating select statement "

Sin título.jpg

That problem is not how to fix it yet.

The other thing is that the external database that joined QV data is constantly being filled , and when to use " reload " or "partial recharge", for some strange reason I duplicated data .

The way I solved it by deleting all data before recharging . But the problem is that it generates must wait 40 minutes ( a long time) so that the data is reloaded all again :

Is there a script that would enable an update of data in the Field without having to remove them?

Is there a script that would enable an update comparing existing data and adding new data only , without adding the ancients?

If you could help me solve one of two problems , I'd appreciate

Not applicable

Re: Reload data

Hello Peter,

I know they are duplicate records because the fields of the added tables possess keys, money and time data that tells me that it is. (especially the money that magically increases sharply with each refill.

And who are new records since it is the basis of data for a sales company which every minute is entering new data to the database.

So, yeah. When I delete the data and return to I load everything from scratch, obviously does not generate duplicates. But this process takes 40 minutes.

That's why I wanted to know if there is a partial reload and how can I control that can generate duplicates?

(The database implemented it with OBDC)

Sin título.jpg

Thank you in advance, David

Not applicable

Re: Reload data

Hi David

If QVD load is taking more time, you can try incremental approach.

Incremental Load in QlikView – Part1 | Learn QlikView

http://www.quickintelligence.co.uk/qlikview-incremental-load/

Go through these links to get an idea on how to do an incremental load.

--Veena

Re: Reload data

QlikView doesn't provide any built-in facilities to perform incremental loads by itself. You have to do it all in the load script by specifying different statements with intelligent WHERE clauses. It boils down to: Step 1: keep the historical data that I loaded the previous time, and determine what was the last record I got. Step 2: load from the source table(s) only those records that are newer than the last record from step 1.

To perform such an Incremental Load there are two basic techniques in QlikView:

  1. Use QVDs to store History locally: at the end of your script, save the current facts table(s) to a (few) QVD file. That will be our history for the next load. At the start of your script, load the QVD if it exists. Decide on what is the last record in this QVD. You'll get either a DateTime value of the last record, or a record number if you are lucky. Then go to your source tables and load&concatenate only those records that are newer than DataTime or whose record number is higher than the one found in the last QVD record.
  2. Use Partial Reload: Another technique is to use a QlikView mode called Partial Reload. In contrast to a Full (regular) reload, QlikView will NOT drop its internal tables, and will only execute those LOAD statements that carry an ADD or REPLACE prefix. Partial Reload can be initiated from the File menu, with a command line switch or in the QMC Task definition.

A variant on the last is to retrieve the historical data from the previous version of your document (note that a QVW is only overwritten when you or the Distribution Service saves it - at the end of the script) by way of a BINARY LOAD at the front of your script.

I'll see if I can find the community document about Incremental loading, and post the link here.

Best,

Peter

Re: Reload data

Some pointers to more information:

QlikView Reference Manual: Chapter 28.4 Using QVD Files for Incremental Load

Community: Re: Dynamic Update and Incremental Loading Gysbert provides a few useful links to discussions about Incremental Loading

Document: Incremental Load Strategies in this discussion: Re: QVD Files

Good luck,

Peter

View solution in original post