20 Replies Latest reply: Jan 25, 2017 10:07 AM by Vladimir Komarov RSS

    Store existing

    Mike Hildner

      How does one go about exporting data that's already part of the app?

       

      For example, I have previously loaded data in a table called "Delivery", and at the beginning of the script I want to export it.

       

      I tried this

      2017-01-24_1342.png

      which resulted in

      2017-01-24_1345.png

      All the examples I've found are using Store after tables are loaded in the script, in this case I want to export what I have first before importing anything.

       

      Regards,

      Mike

        • Re: Store existing
          Sunny Talwar

          May be use this with Binary load?

          Storing all tables in Qlikview file to QVD

          • Re: Store existing
            Vladimir Komarov

            Mike,

             

            You do not need a word "FROM" and '*' in your STORE statement.

            The correct syntax is (for a table named "Calendar"):

            STORE Calendar into FiscalCalendar.csv' (TXT, delimiter is ',');

            STORE Calendar into FiscalCalendar.QVD' (QVD);

             

            Regards,

            Vlad

            • Re: Store existing
              Vladimir Komarov

              I am also trying to understand what do you mean by "I want to export what I have first before importing anything."

              Could you please give more details on that?

              Are you loading a data, doing some transformations and trying to store it after that?

              Or it's more complicated process?

               

              VK

                • Re: Store existing
                  Mike Hildner

                  Hi Vladimir,

                   

                  The syntax change made no difference - same error, according to the Store online help, the're syntactically equivalent.

                   

                  By "I want to export what I have first before importing anything", what I mean is that I want to export out of Sense any data that is part of the application as the first step of a script - to create a backup of existing application data before it is overwritten.

                   

                  I'm starting to think that's not the way it works, that I would need to load data from the qvd first then export.

                    • Re: Store existing
                      Vladimir Komarov

                      Mike,

                       

                      I was assuming you already have some data loaded in the application and you are trying to add some more...

                      In this case you should have the "Delivery" table available in your data model. Is this the situation?

                      Or you do not have any data loaded?

                       

                      VK

                        • Re: Store existing
                          Mike Hildner

                          There's data in the application yes - all my visualizations are working and the data looks good.

                           

                          But then when I go back to the data load editor and run the script, I can't seem to get a reference to data in the application that was first imported on a previous script run.

                            • Re: Store existing
                              Vladimir Komarov

                              Can you check the "Data Model" or "Data Manager" views?

                              You should be able to see all the tables available in your application.  It's possible that the table you are trying to export was dropped or renamed...

                               

                              VK

                                • Re: Store existing
                                  Mike Hildner

                                  Thanks for the replies Vladimir.

                                   

                                  The data is there is the data model viewer, but I think I understand what's happening now. I'm new to Qlik Sense so still figuring out how things work.

                                   

                                  It appears when one runs the script from the data load editor, the first thing that happens is that Sense deletes any previously loaded data. I can verify this by putting in "Exit Script;" as the very first line in the "Main" section. When I do that and run it, the data is gone (the data model viewer is greyed out and if you run the app, there's no data).

                                   

                                  So it appears if I want to export data as the very first step in the script, I would first need to load the .qvd.

                                   

                                  That brings up another thing to figure out, which is that Sense doesn't seem to make a .qvd - the data appears to be stored in a file that's named with a GUID (with no extension). I'll save that for another thread if need be.

                                   

                                  Regards,

                                  Mike

                                    • Re: Store existing
                                      Aehman K

                                      Mike, did you check my reply above?

                                      You can store tables into QVD and use that QVD.

                                      And Sense will not delete your data unless you have given any such command (which I do not know).

                                      You need make a connection...

                                      Once the tables are loaded in script and you load it.

                                      Use the Debug mode on the top right of Edit Script to check for the issue.

                                      And as far as I know you cannot export the Data (rows/columns) from a script. You can do it front end once the tables are loaded, export it to XL or PDF.

                                      • Re: Store existing
                                        Vladimir Komarov

                                        Mike,

                                         

                                        You are correct, Qlik's engine (in both QV and QS) drops all existing data tables before loading the script. 

                                        About your statement: "the data appears to be stored in a file that's named with a GUID" - this file is actually a qlik "application" that stores not only the data, but all UI controls you have created, variables, etc.

                                        You can load this file, using a Binary load like Sunny Twas suggesting above.

                                         

                                        I am still not sure why you need to load QVDs and store them at the end of the script...

                                         

                                        The usual 3-tier process works the following way:

                                        1. Load Data from DB or External files. Do transformation (if needed), store the data into QVDs (multiple QVDs, if applicable).

                                        2. Load previously created QVDs into the application to build a data model (joining, linking the data). Save this file as QVF without creating any UI elements

                                        3. Load the QVF created in step #2 using Binary load and add all necessary UI components. Publish the application created in step #3.

                                         

                                        VK

                                          • Re: Store existing
                                            Sunny Talwar

                                            I wish my suggestion was heard earlier

                                            • Re: Store existing
                                              Mike Hildner

                                              Thanks Vladimir, you've been very helpful. Please do keep in mind that I'm new to Qlik (training is happening over the next couple of weeks).

                                               

                                              >>I am still not sure why you need to load QVDs and store them at the end of the script...

                                               

                                              This is the real use case:

                                               

                                              I get "Delivery" data once a week. This data will always contain year-to-date information, so it will contain duplicates already loaded. There's nothing in the data that would make a unique key (and I have no control over that).

                                               

                                              So, what I'd thought I'd do in the script is to access the data that's already in the application, delete everything that's dated in the current year, then load the new year-to-date data.

                                                • Re: Store existing
                                                  Vladimir Komarov

                                                  Mike,

                                                   

                                                  Do not worry about "novice" part. You are doing pretty good for the guy without any official training... :-)

                                                   

                                                  For your case:

                                                  1. You can always do a full reload for the data you are getting every week. It simplifies the script development and limits sync issues. Disadvantages: the load might take too long...

                                                  2. You can do an incremental load for a new data (if you don't need to "update" the existing records and just load the portion for the current week). In this case you can create a new field in the script:

                                                  //Initial LOAD

                                                  Data:

                                                  Load

                                                  ....

                                                  num(date_field) as Current_Date

                                                  // or date(date_field) as Current_Date

                                                  from (RAW_DATA_SOURCE);

                                                   

                                                  //Assuming you do have some kind of "date_field" in your data....

                                                  // store the result data

                                                  STORE DATA into DATA.QVD (qvd);

                                                   

                                                   

                                                  // incremental load

                                                  // load the existing data first

                                                  DATA:

                                                  LOAD

                                                  *

                                                  From DATA.QVD;

                                                   

                                                  vToday = num(today());

                                                  // LOAD data from new week

                                                  Data:

                                                  Load

                                                  ....

                                                  num(date_field) as Current_Date

                                                  from (RAW_DATA_SOURCE)

                                                  where Current_Date > $(vToday)

                                                  ;

                                                   

                                                   

                                                  Again, it might complicate your script and needs to be carefully structured (especially considering dates' fields),

                                                  but it should help you to reduce the loading time significantly.

                                                   

                                                   

                                                  Regards,

                                                  VK