Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Concatenate Date

 

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

 

2 Replies

Re: Concatenate Date

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]


talk is cheap, supply exceeds demand
Not applicable

Re: Concatenate Date

Thank you it works !!

Community Browser