9 Replies Latest reply: Nov 20, 2015 11:13 AM by Edgar Estrada RSS

    Incremental load for dummies

    Edgar Estrada

      Hi guys

      I read all about the incremental load in the site, including something related to CSVs.

       

      My question is this:

       

      Can I tell sense desktop to get the data from the server that is newer than the last record it has?

      My primary key is YYYY-MM-DD-HH:MM

       

      So the ideal scenario would be:

      1) Query the local data and find the latest record

      2) take that date and put it in a variable or something.

      3) request from my server to get only those records that are newer than the date from step 2

       

      Is that possible?

       

      Thanks in advance for your support.

        • Re: Incremental load for dummies
          Ramon Covarrubias

          I think that is better if you actually store the latest date inside of a qvd and then put that inside a variable instead of storing it in a variable from the beginning

           

           

          check this example, I have not tested it but you should be able to get it to work

           

          load

          MaxDate

          from Maxdate.qvd(qvd);


          let vMaxDate = num(FieldValue('MaxDate',FieldValueCount('MaxDate'))); // this is used in case more than one row is added to the qvd

           

          load

          *;

          sql select

               *

          from yourtable

          where date >'$(vMaxDate)';

           


          let vMaxDate = num(FieldValue('Date',FieldValueCount('Date')));


          MaxDate:

          load

          $(vMaxDate) as MaxDate

          autogenerate(1);


          store MaxDate into Maxdate.qvd(qvd);

          drop table MaxDate ;

            • Re: Incremental load for dummies
              Edgar Estrada

              Ramon

              I am totally new to Qlik Sense Desktop.

               

              I dont know how to do that.

                • Re: Incremental load for dummies
                  Ramon Covarrubias

                  is it failing ?

                  • Re: Incremental load for dummies
                    Ramon Covarrubias

                    added the if condition to check if the file exists or not, let me know if you are having trouble with anything else

                     

                     

                    IF isnull(filetime( 'Maxdate.qvd' ))  THEN // you need to ensure that you are using the correct path for this qvd

                         let vMaxDate = -1;

                    ELSE

                        

                         load

                         MaxDate

                         from Maxdate.qvd(qvd);


                         let vMaxDate = num(FieldValue('MaxDate',FieldValueCount('MaxDate'))); // this is used in case more than one      row is added to the qvd

                    ENDIF

                       

                     

                    load

                    *;

                    sql select

                         *

                    from yourtable

                    where date >'$(vMaxDate)';

                     


                    let vMaxDate = num(FieldValue('Date',FieldValueCount('Date')));


                    MaxDate:

                    load

                    $(vMaxDate) as MaxDate

                    autogenerate(1);


                    store MaxDate into Maxdate.qvd(qvd);

                    drop table MaxDate ;

                      • Re: Incremental load for dummies
                        Edgar Estrada

                        Ramon

                         

                        I really appreciate your support, however I dont know where to insert that code - please excuse my complete ignorance on the tool.

                         

                        So far I managed in Qlik Sense Desktop to:

                        1) read from MSSQL database from a view I created using ODBC

                        2) Play around with graphs, charts and stuff

                        3) Im a little bit concerned about record count because the system adds 90 records every 30 minutes -- I already have like 200k records, and keeps growing every day - thats why I asked for help on the incremental load.

                         

                        Thanks for your patience.

                         

                        -E

                          • Re: Incremental load for dummies
                            Ramon Covarrubias

                            I guess you got a lot of reading to do 

                             

                            https://help.qlik.com/sense/2.0/en-US/online/#../Subsystems/Hub/Content/LoadData/managing-data.htm%3FTocPath%3DWorking%2…

                             

                            this should help with on how to open and add data for sense

                              • Re: Incremental load for dummies
                                Edgar Estrada

                                Ramon:

                                 

                                It works, however is not adding only new records. It completely deletes the history.

                                 

                                Here is the code:

                                 

                                 

                                 

                                SET ThousandSep=',';

                                SET DecimalSep='.';

                                SET MoneyThousandSep=',';

                                SET MoneyDecimalSep='.';

                                SET MoneyFormat='$#,##0.00;-$#,##0.00';

                                SET TimeFormat='hh:mm:ss TT';

                                SET DateFormat='DD-MM-YYYY';

                                SET TimestampFormat='DD-MM-YYYY hh:mm:ss[.fff] TT';

                                SET MonthNames='ene;feb;mar;abr;may;jun;jul;ago;sep;oct;nov;dic';

                                SET DayNames='lun;mar;mié;jue;vie;sáb;dom';

                                SET LongMonthNames='enero;febrero;marzo;abril;mayo;junio;julio;agosto;septiembre;octubre;noviembre;diciembre';

                                SET LongDayNames='lunes;martes;miércoles;jueves;viernes;sábado;domingo';

                                SET FirstWeekDay=6;

                                SET BrokenWeeks=1;

                                SET ReferenceDay=0;

                                SET FirstMonthOfYear=1;

                                SET CollationLocale='es-MX';

                                 

                                 

                                LET vFolder = 'LIB://QVDs/';

                                 

                                 

                                LIB CONNECT TO 'masorganica';

                                 

                                 

                                IF isnull(filetime('$(vFolder)Maxdate.qvd'))  THEN // you need to ensure that you are using the correct path for this qvd

                                     let vMaxDate = '2015-07-26 00:00:05';

                                ELSE

                                   

                                     load

                                     MaxDate

                                     from '$(vFolder)Maxdate.qvd'(qvd);

                                 

                                 

                                     let vMaxDate = FieldValue('MaxDate',1); // this is used in case more than one      row is added to the qvd

                                ENDIF

                                 

                                 

                                [vistaLecturas]:

                                loada

                                *;

                                sql select

                                *  

                                FROM "central"."dbo"."vistaLecturas"

                                where Momento > '$(vMaxDate)' order by momento DESC; //here we get the last date in the DB, we should store it locally

                                 

                                 

                                 

                                let nMaxDate = FieldValue('Momento',1);

                                 

                                 

                                MaxDate:

                                 

                                 

                                load

                                '$(nMaxDate)' as MaxDate

                                autogenerate(1);

                                 

                                 

                                store MaxDate into '$(vFolder)Maxdate.qvd' (qvd);

                                drop table MaxDate ;

                                 

                                 

                                [bombasAuxiliaresActivadas]:

                                LIB CONNECT TO [masorganica];

                                LOAD [Dia],

                                  [Hora],

                                  [Minuto],

                                  [statusBombaAuxiliar1],

                                  [statusBombaAuxiliar2];

                                SQL SELECT  "Dia",

                                  "Hora",

                                  "Minuto",

                                  "statusBombaAuxiliar1",

                                  "statusBombaAuxiliar2"

                                FROM "central"."dbo"."bombasAuxiliaresActivadas";

                                 

                                 

                                 

                                 

                                [sumpTank1SinAgua]:

                                LIB CONNECT TO [masorganica];

                                LOAD [Dia],

                                  [Hora],

                                  [Minuto],

                                  [sumpTank1];

                                SQL SELECT  "Dia",

                                  "Hora",

                                  "Minuto",

                                  "sumpTank1"

                                FROM "central"."dbo"."sumpTank1SinAgua";

                                 

                                 

                                 

                                 

                                [sumpTank2SinAgua]:

                                LIB CONNECT TO [masorganica];

                                LOAD [Dia],

                                  [Hora],

                                  [Minuto],

                                  [sumpTank2];

                                SQL SELECT  "Dia",

                                  "Hora",

                                  "Minuto",

                                  "sumpTank2"

                                FROM "central"."dbo"."sumpTank2SinAgua";

                      • Re: Incremental load for dummies
                        Sangram Reddy

                        Hi Edgar,

                         

                        All the questions you have asked for are absolutely possible.

                         

                        Please go through this document for deeper insight: Incremental Load in Qlikview - Sources

                         

                        Incremental load is the same with QlikView or QlikSense