Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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 ;
Ramon
I am totally new to Qlik Sense Desktop.
I dont know how to do that.
is it failing ?
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 ;
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
I guess you got a lot of reading to do
this should help with on how to open and add data for sense
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";
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
Thank you so much, Sangram!
On Fri, Nov 20, 2015 at 8:27 AM, sangram Reddy <qcwebmaster@qlikview.com>