Change date format from integer to normal after using crosstab

[#1]

Please help me with the syntax for converting the integer date below to 'MMM-YYYY'. 

CrossTable(ForecastDate, ForecastQty, 3)

LOAD Franchisee,

     Item,

     ForecastCategory,

     [42005],

     [42036],

     [42064],

     [42095],

     [42125],

     [42156],

     [42186],

     [42217],

     [42248],

     [42278],

     [42309],

     [42339]

FROM

(ooxml, embedded labels, table is FranchiseEquipmentForecast);

Here's the part that is not working:

LOAD

ForecastDate,

date(Date#(ForecastDate,'MMM-YYYY'),'MMM-YYYY') as ForecastDate,

RESIDENT Crosstab;

[kavita25]

hi,

Following is the script as per the excel sheet which have been attached already in my previous comment.

Test:

CrossTable(Date,Values,3)

LOAD Franchisee,

     Item,

     ForecastCategory,

     [42005],

     [42036],

     [42064],

     [42095],

     [42125],

     [42156],

     [42186],

     [42217],

     [42248],

     [42278],

     [42309],

     [42339]

FROM

[..\Extrass\Testonexcel.xlsx]

(ooxml, embedded labels, table is Sheet1);

Test1:

load*,

date(DATE,'MMM-YYYY') as TestDate;

load*,

(Num#(Date)) as DATE,

'Test' as Testflag

Resident Test;

DROP Table Test;

exit Script

set The date format in Main tab as M-D-YYYY

And the output you can see in the below snapshot.

Regards,

Kavita

[MarcoWedel]

Hi,

one solution could be:

tabFranchise:
CrossTable(ForecastDateTemp, ForecastQty, 3)
LOAD *
FROM [http://community.qlik.com/servlet/JiveServlet/download/692643-144570/FranchiseEquipmentForecast_2015...]
(ooxml, embedded labels, table is FranchiseEquipmentForecast);

Left Join (tabFranchise)
LOAD Distinct
  ForecastDateTemp,
  Date(Num#(ForecastDateTemp),'MMM-YYYY') as ForecastDate
Resident tabFranchise;

DROP Field ForecastDateTemp;

If you can use LOAD * instead of defining the column names, your script will be more generic and work for excel files with different column names (months/dates) as well.

Hope this helps

regards

Marco

[#4]

Fantastic, worked like a charm.  Thanks a mill.

[sunilkumarqv]

Yes it works !

but its  to much effecting  on performance

Hi @Manus Mentz

do tell how is the performance when use use num# in script?