Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 )
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
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
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
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 "
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
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)
Thank you in advance, David
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
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:
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
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