Date Conversion

[Anonymous]

Hi Guys,

I have a requirement to convert the excel into straight table. I am able to do that but there is a field which is a date field and when it's pulled in to application, it gets converted to number and despite of many trials I am not able to get the date from it. Please find attached the excel and my QVW in progress.

Basically, in qlikview I need the follwoing result

Field1	Field2	MonthYear	Data
a	b	11 March 2014	23
a	b	11 December 2014	45
c	d	11 March 2014	67
c	d	11 December 2014	89

[beck_bakytbek]

hi neha,

try to use the year(), month() in script,

beck

[kaushiknsolanki · Accepted solution]

Hi,

Try this.

Load Mnth as MonthName, Date(Num#(Mnth),'DD MMM YYYY') as MMMM Resident 1;

Its because the number which you are loading is in Text format.

Regards,

Kaushik Solanki

[sunny_talwar]

Try adding Num#() to your resident load:

1:

CrossTable(Mnth, Data, 2)

LOAD * FROM

Date.xlsx

(ooxml, embedded labels, table is Sheet1);

LOAD Mnth as MonthName,

  Date(Num#(Mnth),'DD MMM YYYY') as MMMM

Resident 1;

[Anonymous]

Thanks for the immediate response guys, it helped!

Num# is something I was missing there.

Thanks,

Neha

[rik_qlik]

Hi neha sharma,

As Kaushik Solanki highlight, it seems the crosstable(..) function doesn't keep the original date format from excel during the table transformation..

In fact you can see the field 'Mnth' is on the left side of your list box, because it's interpret as a text field after the transformation of the function crosstable(..).

So you must use the 'Num#(Mnth)' function in the script to pass to Qlik the right format information.

So if you use the 'Date(Num#(Mnth),'DD MMM YYYY') as MMMM' and you put the 'MMMM' field in a list box you can see what you want achieve.

Regards,

Riccardo C.