topic Re: Change date format from integer to normal after using crosstab in QlikView

Change date format from integer to normal after using crosstab

Fri, 09 Jan 2015 19:37:26 GMT

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;

Re: Change date format from integer to normal after using crosstab

its_anandrjs — Fri, 09 Jan 2015 19:50:54 GMT

Try before the cross table provide any sample files for the same.

Regards

Anand

Re: Change date format from integer to normal after using crosstab

MarcoWedel — Fri, 09 Jan 2015 19:52:51 GMT

Left Join

LOAD distinct

ForecastDate,

date(ForecastDate,'MMM-YYYY') as ForecastDate2

Resident Crosstab;

Re: Change date format from integer to normal after using crosstab

Sat, 10 Jan 2015 04:53:30 GMT

Heres a sample of the file I'm importing using Cross Table:

Re: Change date format from integer to normal after using crosstab

Sat, 10 Jan 2015 04:55:06 GMT

Hi Marco. Tx for the help. I still get an error on you script: "Table not found".

I've added a sample of the file on Anand's reply if that will help

Re: Change date format from integer to normal after using crosstab

kavita25 — Sat, 10 Jan 2015 06:30:08 GMT

Hi,

Check the given QVW and excel as per your given picture.

I think, it can solve your issue.

Regards,

Kavita

Re: Change date format from integer to normal after using crosstab

its_anandrjs — Sat, 10 Jan 2015 07:10:58 GMT

Can you provide it in excel format.

Regards

Anand

Re: Change date format from integer to normal after using crosstab

Sat, 10 Jan 2015 08:20:20 GMT

Heres a sample of the file:

Re: Change date format from integer to normal after using crosstab

Sat, 10 Jan 2015 08:21:12 GMT

Hi Kavita, can you paste the script in the QVW file, I have personal edition, so I can't open it this side. Many thanks

Re: Change date format from integer to normal after using crosstab

its_anandrjs — Sat, 10 Jan 2015 08:29:46 GMT

Hi,

Then load another resident table and do transformation for the ForecastDate because it is not in date format first convert it into date format and then Date format to achieve the result and format date as 'MMM-YYYY'.

Crosstab:
CrossTable(ForecastDate, ForecastQty, 3)

LOAD Franchisee,
Item,
ForecastCategory,
[42005],
[42036],
[42064],
[42095],
[42125],
[42156],
[42186],
[42217],
[42248],
[42278],
[42309],
[42339]
FROM
FranchiseEquipmentForecast_2015_Sample.xlsx
(ooxml, embedded labels, table is FranchiseEquipmentForecast);

LOAD ForecastDate,
Date(Num#(ForecastDate,'MMM-YYYY'),'MMM-YYYY') as ForecastDateNew
Resident Crosstab;

Regards

Anand

Re: Change date format from integer to normal after using crosstab

kavita25 — Sat, 10 Jan 2015 09:28:31 GMT

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

Re: Change date format from integer to normal after using crosstab

MarcoWedel — Sat, 10 Jan 2015 10:24:01 GMT

Hi,

one solution could be:

tabFranchise:
CrossTable(ForecastDateTemp, ForecastQty, 3)
LOAD *
FROM [http://community.qlik.com/servlet/JiveServlet/download/692643-144570/FranchiseEquipmentForecast_2015_Sample.xlsx]
(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

Re: Change date format from integer to normal after using crosstab

Sat, 10 Jan 2015 19:11:18 GMT

Fantastic, worked like a charm. Thanks a mill.

Re: Change date format from integer to normal after using crosstab

sunilkumarqv — Thu, 21 May 2015 07:36:10 GMT

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?