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
