9 Replies Latest reply: Apr 10, 2016 8:39 AM by Stefan Wühl RSS

    Load New Data

    Deepanshu Chamoli

      Hello All

       

      Please help me in the below query. I read the qlik documents but still confused.

       

      I have connected ODBC with Qlik Sense. In SQL the data is updated every day. As there are millions of data approx 200 million data, I need to load the new data only so that it takes less time to upload the data in qvd.

       

      I have created a variable using LET statement which contains maximum date. I am using the variable in the where statement to get the new data.

       

      The issue is when I load the data first time it loads the new data. But when I load it again it shows that data is loaded again which is not correct. The output must be 0 ideally

       

      As per me the mistake is creating the variable and passing the variable. I have created a variable which picks the maximum post date from the table

       

      I have passed this variable in the script in where clause as

       

      Temp:

      LOAD

          Id,

          "Date"

      FROM [lib://Files/Incremental.xlsx]

      (ooxml, embedded labels, table is Sheet1)

      where "Date" > $(varMAXDATE);

       

       

      Incta:

      Load

      DATE(MAX("Date")) as MAXDATE

      Resident Temp;

       

       

      LET varMAXDATE = PEEK('MAXDATE',0,'Incta');

       

      I don’t have the qvd as well as script the qvd is in other environment.

       

      Can someone please guide the best method for incremental load in qvd and correct me also.

       

      sunindia

      swuehl

      maxgro

       

      I will be grateful if someone attach the qvd where the above example suits

       

      Thanks

      Deepanshu

        • Re: Load New Data
          Stefan Wühl

          Try

           

          LET varMAXDATE =Num(PEEK('MAXDATE',0,'Incta'));


          and check that the variable is set correctly to a number after reload.


          [I believe your formatted date won't be recognized as date after dollar sign expansion, so you could also try to embed the variable in single quotes: where "Date" > '$(varMAXDATE)';  but I would try the first suggested approach using Num() first]

            • Re: Load New Data
              Deepanshu Chamoli

              Thanks sir.

               

              I used both NUM and ' ' as per your suggestion.

               

              I have attached the snapshot numbered 1,2 & 3. With your help now I am able to show the same what problem I faces while working on client machine

               

              1. I load the data once, it shows that the new data is loaded successfully (Works fine)
              2. I loaded the data again, the show that no record is loaded (Works Fine)
              3. I loaded the data again, it shows that all the data is loaded again ( Not working fine)

               

              Please tell me that how numbered 3 is resolved, means even load multiple times it shows output 0 after the new data is loaded successfully

               

              Temp:

              LOAD

                  Id,

                  "Date"

              FROM [lib://Files/Incremental.xlsx]

              (ooxml, embedded labels, table is Sheet1)

              where "Date" > '$(varMAXDATE)';

               

               

              Incta:

              Load

              DATE(MAX("Date")) as MAXDATE

              Resident Temp;

               

               

              LET varMAXDATE = num(PEEK('MAXDATE',0,'Incta'));

               

              Please guide

               

              Thanks in advance!!

                • Re: Load New Data
                  Stefan Wühl

                  Well, have you checked the variable you are creating and the  Incta table?

                   

                  You'll see that on the second reload, the Incta table shows zero records, so not showing the max date you expect to get.

                   

                  I would recommend that you retrieve your max date not from the previous load, but from looking into your historic QVD(s) you are using a base to add the new records to.

                   

                  I also recommend looking into existing samples for an incremental load approach. There is a nice sample by Rob Wunderlich:

                  http://qlikviewcookbook.com/download/delta-load-template/

                    • Re: Load New Data
                      Deepanshu Chamoli

                      Thanks sir!!

                       

                      I am very new to Qlik Sense, I tried to follow the above but fail.

                       

                      It would be great if you brief me the steps so that I can apply it

                       

                      Deepanshu

                        • Re: Load New Data
                          Stefan Wühl

                          Hi Deepanshu,

                           

                          you have tried exactely which steps and where does this fail? Do you get a script error when executing or unexpected results?

                           

                          Could you post your current script?

                            • Re: Load New Data
                              Deepanshu Chamoli

                              Sir, I tried to replicate the same.

                               

                              But it looks very hard to replicate, sorry for that.

                               

                              Can you please tell me the other way so that the problem resolve

                               

                              Reagrds

                              Deepanshu

                                • Re: Load New Data
                                  Stefan Wühl

                                  Still not sure what exactely you have tried, but you can try with your original approach like

                                   

                                  Temp:

                                  LOAD

                                      Id,

                                      "Date"

                                  FROM [lib://Files/Incremental.xlsx]

                                  (ooxml, embedded labels, table is Sheet1)

                                  where "Date" > '$(varMAXDATE)';

                                   

                                   

                                  Incta:

                                  Load

                                  DATE(MAX("Date")) as MAXDATE

                                  Resident Temp;

                                   

                                   

                                  LET varMAXDATE = RangeMax( num(PEEK('MAXDATE',0,'Incta')), varMAXDATE);

                                    • Re: Load New Data
                                      Deepanshu Chamoli

                                      Thanks a lot sir for the help!!

                                       

                                      I tried it but first time when I load the data it fetch only new data.

                                       

                                      Second time when I load the data it pulls no record(ideally it is correct),

                                       

                                      But in visualization due to 0 data load all the fields are empty. Somehow all the data becomes 0

                                       

                                      Do we need to do concatenate it with actual one

                                       

                                      Thanks in advance!!

                                       

                                      Regards

                                      Deepanshu