topic Re: Load Date and MontName Function in QlikView

Load Date and MontName Function

pgarcia75 — Fri, 18 Sep 2015 10:02:00 GMT

Hello

I charge date from a MySQL database that I want to convert function with montname function

The latter gives me names in English while the xx variable exists in French.

In addition, after help in line, I should have the month and year

Who can help me ?

Thank you

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 LongMonthNames='Janvier;Fevrier;Mars;Avril;Mai;Juin;Juillet;Aout;Septembre;Octobre;Novembre;Decembre';
SET DayNames='lun.;mar.;mer.;jeu.;ven.;sam.;dim.';

ODBC CONNECT TO Mediweb (XUserId is BLIVRbVMCTWA, XPassword is aSSdVbVMCDQB);

SQL SELECT
    no,
   `periode`,
`urm`,
`service`,
   MonthName(`periode`) as mois,
   year(`periode`) as annee
FROM mediweb.stat;

Re: Load Date and MontName Function

MK_QSL — Fri, 18 Sep 2015 10:02:51 GMT

You need to change system variables

SET MonthNames='janv.;févr.;mars;avr.;mai;juin;juil.;août;sept.;oct.;nov.;déc.';
SET LongMonthNames='Janvier;Fevrier;Mars;Avril;Mai;Juin;Juillet;Aout;Septembre;Octobre;Novembre;Decembre';
SET DayNames='lun.;mar.;mer.;jeu.;ven.;sam.;dim.';

Change them to english..!!

Re: Load Date and MontName Function

pgarcia75 — Fri, 18 Sep 2015 11:51:08 GMT

The result is in english but I would have in french !

How I can do ?

Re: Load Date and MontName Function

hic — Fri, 18 Sep 2015 12:50:50 GMT

Since the Year and MonthName functions are in the SELECT statement, they are evaluated by the ODBC driver, and not by the QIX engine. Try instead

Load

no,

`periode`,

`urm`,

`service`,

MonthName(`periode`) as mois,

year(`periode`) as annee;

SQL SELECT * FROM mediweb.stat;

where the functions are in the Load - which is evaluated by the QIX engine.

HIC

Re: Load Date and MontName Function

sasiparupudi1 — Fri, 18 Sep 2015 12:59:57 GMT

not clear, can you please give an example?

Re: Load Date and MontName Function

Anonymous — Fri, 18 Sep 2015 13:03:05 GMT

I agree with henric

We can't use the Qlikview logis such as Month Name and "as" in the SQL statement.

So we need to do the preceeding load statements before your SQL select table.Use Henric's codes

Re: Load Date and MontName Function

pgarcia75 — Fri, 18 Sep 2015 14:20:00 GMT

Great it's working !!

Thanks for all

Re: Load Date and MontName Function

satishkurra — Fri, 18 Sep 2015 14:21:46 GMT

Thanks Henric for your valuable answer:)