-
Re: add data to basic table
youssef belloum Dec 18, 2017 5:40 AM (in response to Daniel Kuba)Hi,
all you need to know is here:
Loading new and updated records with incremental load ‒ Qlik Sense
https://www.quickintelligence.co.uk/qlikview-incremental-load/
-
Re: add data to basic table
Daniel Kuba Dec 18, 2017 6:44 AM (in response to youssef belloum)HI, thank you. I gues that I need this one:
Insert only (no update or delete)
If the data resides in a database other than a simple log file, the append approach will not work. However, the problem can still be solved with a minimum amount of extra work. The following conditions apply:
- The data source can be any database.
- Qlik Sense loads records inserted in the database after the last script execution.
- A ModificationTime field (or similar) is required for Qlik Sense to recognize which records are new.
Example:
QV_Table:
SQL SELECT PrimaryKey, X, Y FROM DB_TABLE
WHERE ModificationTime >= #$(LastExecTime)#
AND ModificationTime < #$(BeginningThisExecTime)#;
Concatenate LOAD PrimaryKey, X, Y FROM File.QVD;
STORE QV_Table INTO File.QVD;
The hash signs in the SQL WHERE clause define the beginning and end of a date. Check your database manual for the correct date syntax for your database.
But still Im not sure how write it in script. My english isnt best for understand wholle text.
-
-
Re: add data to basic table
Andrea Gigliotti Dec 18, 2017 5:53 AM (in response to Daniel Kuba)are you using a database connection ?
-
Re: add data to basic table
Daniel Kuba Dec 18, 2017 6:39 AM (in response to Andrea Gigliotti )I using autogenerated xml files like new tabs
-
Re: add data to basic table
Andrea Gigliotti Dec 18, 2017 6:43 AM (in response to Daniel Kuba)so you can do a loop reading all *.xml files in a folder, then you can create a task with QMC (if you have Qlik Sense Server) and schedule it to run automatically.
-
Re: add data to basic table
Daniel Kuba Dec 18, 2017 6:46 AM (in response to Andrea Gigliotti )sorry, its txt files
-
Re: add data to basic table
Daniel Kuba Dec 18, 2017 6:47 AM (in response to Daniel Kuba)I have qlink sense desktop version
-
Re: add data to basic table
Andrea Gigliotti Dec 18, 2017 7:00 AM (in response to Daniel Kuba)ok let's try using something like this:
SET searchStr = '*.txt';
SET RootPath = ''; // your folder path
for each File in filelist (RootPath&searchStr)
CONCAT (Basictable)
LOAD *
from $(File)
(txt, codepage is 1252, explicit labels, delimiter is '\t', msq);
next File
but with Qlik Sense Desktop you can't run the app in automatically way.
-
Re: add data to basic table
Daniel Kuba Dec 18, 2017 8:27 AM (in response to Andrea Gigliotti )Thank you. But maybe I wrote it wrong. Sorry for my bad english. I havent problem, that tables dont concatenate. My problem is, that application isnt automaticaly updated about new data from new files, when I open it.
I must go to data load editor and press button "Load data" and system load all data and it take many time. I have many huge files. But my old data dont change, so I only need add new data to "old table" each day.
Now I must press button "Load data" and then system load all files from before - today example 300 files, tomorrow 301, after tomorrow 302 files. And only loading data take me cca 10 min each day.
But to save time for me will be better load each day only 1 new file and add it to table that contain all my old data. I gues that is some possible make with variables, but I dont know how.
Sorry for my english and confusing you.
-
-
-
-
-
-
Re: add data to basic table
ramesh g Dec 18, 2017 6:06 AM (in response to Daniel Kuba)Hi, You can use Partial Reload..... Add only Condition.
-
Re: add data to basic table
Andrea Gigliotti Dec 18, 2017 6:16 AM (in response to ramesh g)Partial Reload is only available in QlikView with Publisher enterprise license.
-
-
Re: add data to basic table
Martin Pohl Dec 18, 2017 6:30 AM (in response to Daniel Kuba)Qlik automatically concat tables with same fields (as you wrote, newtable have same fields).
if not neccesary, do not join, just link the tables)
regards
-
Re: add data to basic table
Daniel Kuba Dec 18, 2017 6:38 AM (in response to Martin Pohl )I know, that is concatenate automated. But when I run Qlik Sense, I want get table with new data. But to get new data I must first in dataload editor press button Load to get new data.
-
Re: add data to basic table
Martin Pohl Dec 18, 2017 7:11 AM (in response to Daniel Kuba)for sure.
how would you get your data?
you can insert the delta load script in your script so you have to reload only one script.
or the Delta load script is running continously so you create new datas near to realtime
-
Re: add data to basic table
Daniel Kuba Dec 18, 2017 8:17 AM (in response to Martin Pohl )I thought, that I can make some variable to automatically load new data and add it to data that were there before, when I open application. When I use "Load data" in dataload editor, it take too much time, because I have huge data files and so many. And all files load again, but old data dont change, I only need join new data table only. It take different time, when system load 301 files or only new 1 file add to 300 old files, that were loaded before.
What is delta script? Whit codes Im not too strong.
Sorry for my bad english.
-
Re: add data to basic table
Martin Baculik Dec 18, 2017 8:45 AM (in response to Daniel Kuba)Hi Daniel,
what about different approach?
First, load all your 300 files and store them into QVD file. I.e.: (INITIAL LOADER)
------------
Table:
Load *
FROM YourLibraryPath\*.txt;
store Table into YourLibraryPath\YourFile.qvd (qvd);
------------
now you can remove INITIAL LOADER code above and do next:
Second, attach only your new data from txt file to existing qvd file. I.e.: (REGULAR LOADER)
-------------
Table:
Load *
FROM YourLibraryPath\YourFile.qvd (qvd);
concatenate(Table)
Load *
FROM YourLibraryPath\YourLatestTXTFile.txt;
store Table into YourLibraryPath\YourFile.qvd (qvd); //This would attach only new data set to existing data
----------
Third, you can use YourFile.qvd in final application. (FINAL APPLICATION)
---------
Load *
From YourLibraryPath\YourFile.qvd (qvd);
--------
You can split your loads into smaller parts that could be helpful when doing reloads. Example above is very basic, I do not know if you have any other constraints on your side.
Thanks.
BR
Martin
-
-
-
-