Qlik Community

Qlik Sense Data Connectivity

Discussion board where members can learn more about Qlik Sense Data Connectivity.

edgarestrada
New Contributor II

Incremental load for dummies

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.

Tags (1)
9 Replies
ramoncova06
Valued Contributor III

Re: Incremental load for dummies

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
New Contributor II

Re: Incremental load for dummies

Ramon

I am totally new to Qlik Sense Desktop.

I dont know how to do that.

ramoncova06
Valued Contributor III

Re: Incremental load for dummies

is it failing ?

ramoncova06
Valued Contributor III

Re: Incremental load for dummies

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
New Contributor II

Re: Incremental load for dummies

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
Valued Contributor III

Re: Incremental load for dummies

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
New Contributor II

Re: Incremental load for dummies

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

reddys310
Honored Contributor II

Re: Incremental load for dummies

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
New Contributor II

Re: Incremental load for dummies

Thank you so much, Sangram!

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

Community Browser