Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Date interpretation in incremental load

Hi everybody.

I had an issue with my script (which is only a proof of concept in order to implement it i a larger qvw afterward).

I've tested it but it only load the entries within the QVD file and not from teh ODBC data.

I assume that the problem is coming from the WHERE close of the select query.

When you look at the Sage forum about their ODBC driver (...) many post talk about a specific format which is : {d YYYY-MM-DD}

In another script I have a where close which is as foloowing : Date > 2012-01-01

and it works

So I've tried different combination for my incremental script and when you look at the reference book, they give a #$(variable)# as example assuming its a number comparison. But with the format given previously I think their should be a problem where the sql query should do a date comparison.

Here is my script whether one of you as an idea on how to handle my issue.

SET ThousandSep=' ';

SET DecimalSep=',';

SET MoneyThousandSep=' ';

SET MoneyDecimalSep=',';

SET MoneyFormat='# ##0,00 €;-# ##0,00 €';

SET TimeFormat='hh:mm:ss';

SET DateFormat='DD/MM/YYYY';

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

SET MonthNames='janv.;févr.;mars;avr.;mai;juin;juil.;août;sept.;oct.;nov.;déc.';

SET DayNames='lun.;mar.;mer.;jeu.;ven.;sam.;dim.';

ODBC CONNECT TO [SageBijouxLocal;DBQ=C:\Users\Public\Documents\Sage\Gestion commerciale\Gescom Bijou.gcm] (XUserId is cfMGLeEIELbSGXFNTDdEXQROFLdEHfAF);

Let MomentDernExéc = '{d ' & Date(ReloadTime(),'YYYY-MM-DD') &'}';

Let ExecTime = '{d ' & Date(Now(),'YYYY-MM-DD') &'}';

Articles:

SQL SELECT * FROM "F_ARTICLE"

WHERE AR_DATEMODIF >= $(MomentDernExéc) AND AR_DATEMODIF < $(ExecTime);

IF Not IsNull(QvdCreateTime('Bijoux_Articles.qvd')) THEN

Concatenate (Articles)

LOAD * FROM Bijoux_Articles.QVD (qvd) WHERE NOT Exists(AR_REF);

END IF

IF NoOfRows(Articles) > 0 THEN

Let vMessage = NoOfRows(Articles) & ' nouvel article';

MsgBox( $(vMessage), 'msgbox', 'OK', 'ICONASTERISK');

STORE Articles INTO Bijoux_Articles.QVD (qvd);

END IF

1 Reply
Not applicable

Re: Date interpretation in incremental load

In case one of you have the issue one of this day, I find he answer. The problem came from the variable init and format, which has to be done by a SET statement instead of LET, and for SAGE i could be directly in the 'YYYY-MM-DD' format.

Community Browser