topic Re: Incremental load for dummies in Connectivity & Data Prep

Incremental load for dummies

edgarestrada — Wed, 19 Aug 2015 01:07:29 GMT

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

ramoncova06 — Wed, 19 Aug 2015 15:28:15 GMT

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

edgarestrada — Mon, 24 Aug 2015 02:39:32 GMT

Ramon

I am totally new to Qlik Sense Desktop.

I dont know how to do that.

Re: Incremental load for dummies

ramoncova06 — Mon, 24 Aug 2015 16:45:55 GMT

is it failing ?

Re: Incremental load for dummies

ramoncova06 — Mon, 24 Aug 2015 16:55:36 GMT

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

edgarestrada — Tue, 25 Aug 2015 03:33:14 GMT

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

ramoncova06 — Tue, 25 Aug 2015 17:49:35 GMT

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

edgarestrada — Thu, 27 Aug 2015 05:50:16 GMT

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

reddy-s — Fri, 20 Nov 2015 14:26:52 GMT

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

Re: Incremental load for dummies

edgarestrada — Fri, 20 Nov 2015 16:13:06 GMT

Thank you so much, Sangram!

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