topic Re: Date Conversion in QlikView

Date Conversion

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Date Conversion

beck_bakytbek — Mon, 01 Aug 2016 10:20:38 GMT

hi neha,

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

beck

Re: Date Conversion

kaushiknsolanki — Mon, 01 Aug 2016 10:21:04 GMT

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

Re: Date Conversion

sunny_talwar — Mon, 01 Aug 2016 10:22:06 GMT

Try adding Num#() to your resident load:

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;

Re: Date Conversion

Anonymous — Mon, 01 Aug 2016 10:51:16 GMT

Thanks for the immediate response guys, it helped!

Num# is something I was missing there.

Thanks,

Neha

Re: Date Conversion

rik_qlik — Mon, 01 Aug 2016 11:05:18 GMT

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.