Concatenate Date

[#1]

Hi,

I would like to load an excel file (.xlsx) in qlik sense.

I succeeded in loading it but I have some difficulties with dates.

I have 2 columns in my excel file:

1 column (format text) with month and year. (In French >> E.g.: Août 2014)

I convert this column into date with the function :

   Date(Date#([Mois Année],'MMMM YY'),'MM/YYYY') as [MOIS ANNEE],

-1 column (format text) with days. (In French >> E.g.: 01)

I convert this column into date with the function :

   Day(Date#(Jour,'DD')) as JOUR

I would like to create a third column to concatenate my colums JOUR and [MOIS ANNEE] and to convert it into date format.

I would like date like this : DD/MM/YYYY.

I tried this function, but it doesn’t works :

    Date(Date#(JOUR&[MOIS ANNEE],'DD MMMM YY'),'DD/MM/YYYY') as [DATE ECRITURE]

Have you an idea to help me ?

Here Is my script :

[JOURNAL]:

LOAD

     Journal as JOURNAL,

     [Mois Année],

     Jour,

     [N° Pièce] as [N° PIECE],

     Compte as [COMPTE],

     [N° Auxiliaire] as [N° AUXILIAIRE],

     Libellé as LIBELLE,

     Réf. as REFERENCE,

     Lett. as LETTRAGE,

     Débit as DEBIT,

     Crédit as CREDIT,

     Date(Date#([Mois Année],'MMMM YY'),'MM/YYYY') as [MOIS ANNEE],

     Year(Date#([Mois Année],'MMMM YY')) as ANNEE,

     Month(Date#([Mois Année],'MMMM YY')) as MOIS,

     Day(Date#(Jour,'DD')) as JOUR

FROM [lib://TDB/Journaux MEP N.xlsx]

  (ooxml, embedded labels, table is [Journaux MEP N])

Where Journal <'Total' or Journal >'TotalZ' ;

[JOURNAL2]:

LOAD

  Date(Date#(JOUR&[MOIS ANNEE],'DD MMMM YY'),'DD/MM/YYYY') as [DATE ECRITURE]

Resident [JOURNAL]

You will fin an exemple of my file in attached file.

Thank you very much

Julien

[Gysbert_Wassenaar]

Make sure to set the names of the months to the French values in the LongMonthNames variable. And try adding a space between:

Date(Date#(JOUR & ' ' & [MOIS ANNEE],'DD MMMM YY'),'DD/MM/YYYY') as [DATE ECRITURE]

[#3]

Thank you it works !!