Incremental load for dummies

[edgarestrada]

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.

[ramoncova06]

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 ;

[edgarestrada]

Ramon

I am totally new to Qlik Sense Desktop.

I dont know how to do that.

[ramoncova06]

is it failing ?

[ramoncova06]

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 ;

[edgarestrada]

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

[ramoncova06]

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%3...

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

[edgarestrada]

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";

[reddy-s]

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

[edgarestrada]

Thank you so much, Sangram!

On Fri, Nov 20, 2015 at 8:27 AM, sangram Reddy <qcwebmaster@qlikview.com>