2 Replies Latest reply: May 5, 2015 9:44 AM by Julien CHOLLET RSS

    Concatenate Date

    Julien CHOLLET

       

      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