16 Replies Latest reply: Dec 18, 2017 8:45 AM by Martin Baculik RSS

    add data to basic table

    Daniel Kuba

      Hello, I have one basic table and I made application for it. How I add new table in script? To automated add table with opening application. I shall add each day new one to old. I know, that is something like incremental load.

      Condition - all tables are same, only values inside new tables are different and name of new table is different (part of name is date).

       

      Example:

       

      I have table with name:                     Basictable
      second day                                       Newtable01

      third day                                            Newtable02

      fourth day                                          Newtable03

      etc.

       

      and I need join newtable each day to basic table. But still I need old data from tables that were inside before.

          • Re: add data to basic table
            Daniel Kuba

            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

            are you using a database connection ?

            • Re: add data to basic table
              ramesh g

              Hi, You can use Partial Reload..... Add only Condition.

              • Re: add data to basic table
                Martin Pohl

                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

                    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

                        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

                            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

                                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