6 Replies Latest reply: Dec 14, 2015 1:18 PM by Olivier Sinquin RSS

    Unable to reformat date imported from Excel

      I'm importing data from Excel that includes date information (YYYY-MM).

      I need to get 2 new separate columns for YYYY and MM.

      Unfortunately, whatever I try in QlikView and Qlik Sense it results in empty data for the formatted date.

       

      typeKPIjanv-14janv-15
      1A1112
      2B2122


      year is empty.png

       

      I used  date formatting successfully in other scripts (e.g. year([Day]) as "Year") but this time around it just doesn't work and I have no clue why.


      Here is my script (I pasted everything even if don't think my issue has anything to do with the crosstable, because even when I try to use main assets I have the same issue).

      NB: I also tried using the Date function - Date([Month], 'YYYY') as "year" but it didn't make a difference


      initial:

      CrossTable(Month, Data, 2)

      LOAD * FROM

      [lib://IT KPIs Data\Funds Admin\KPIs.xlsx]

      (ooxml, embedded labels, table is Feuil1);

       

      final:

      LOAD

      KPI,

      Data,

      Month,

      Year([Month]) as "year",

      RESIDENT initial;

       

      drop table initial;