topic Re: Date as Column Titles from Excel in QlikView

Date as Column Titles from Excel

Tue, 18 Aug 2015 08:38:12 GMT

Hi all,

I'm importing data from excel file. The problem is columns of excel file are dates. To be clear, I've data like this :

Products Jan-2015 Feb-2015 Mar-2015 ... ... ...

product1 100 150 200

product2 110 175 210

product3 120 180 220 etc...

When I load from excel, the column names imported like 42005, 42036, 42061 etc. And I need to make a monthly report chart from this data. And I want to get SUM(Jan-2015), SUM(Feb-2015) etc..

Any suggestions?

Thank you.

Re: Date as Column Titles from Excel

MK_QSL — Tue, 18 Aug 2015 08:41:53 GMT

Temp:

CrossTable(MonthYear, Value)

Load

From TableName;

NoConcatenate

Final:

Load

Products,

Date(Date#(MonthYear,'MMM-YYYY'),'MMM-YYYY') as MonthYear,

Value

Resident Temp;

Drop Table Temp;

Re: Date as Column Titles from Excel

vcanale — Tue, 18 Aug 2015 09:05:48 GMT

Hi,

this sounds weird, the labels should be imported as they are..

anyway you can change the column names in the load statement:

Directory;

LOAD Products,

42005 as [Jan-2015],

42036 as [Feb-2015],

42061 as [Mar-2015],

FROM...

Regards

Re: Date as Column Titles from Excel

kamielrajaram — Tue, 18 Aug 2015 09:13:39 GMT

Hi,

You could format your excel file by adding an apostrophe in front of the months 'Jan-2015

Kamiel

Re: Date as Column Titles from Excel

jonathandienst — Tue, 18 Aug 2015 09:49:20 GMT

QV is importing the date numbers underlying the dates in Excel. This is a minor tweak to Manish's script to handle date numbers:

Temp:

CrossTable(MonthYear, Value)

Load

From TableName;

NoConcatenate

Final:

Load

Products,

Date(MonthYear, 'MMM-YYYY') as MonthYear,

Value

Resident Temp;

Drop Table Temp;

Re: Date as Column Titles from Excel

sasiparupudi1 — Tue, 18 Aug 2015 11:19:36 GMT

QV and excel interpret dates using the internal values.(if you copy the excel data and paste it into a new sheet as values, you could see excel replacing the dates into numbers)

I suggest you do the transformation of the dates in excel by using the formula before loading.

TEXT("2015/01/01","mmm-yyyy")

otherwise you should use the way as suggested by Manish and Jonanthan

hth

Sasi

Re: Date as Column Titles from Excel

Tue, 18 Aug 2015 13:32:46 GMT

Hi,

You can rename the field names as suggested by VCanaleContext

Regards,

Sangeetha S

Re: Date as Column Titles from Excel

keshavkumar — Mon, 04 Dec 2017 06:40:20 GMT

Thanks, this actually helped it.

Regards,

Keshav

Re: Date as Column Titles from Excel

shalensookdeo — Tue, 18 Sep 2018 18:46:38 GMT

I am having a similar challenge. Please can you advise me on the following table? This is my challenge, i am using the following excel template as a sample but my data actually pulls from a sql database with the following headers but i want them to be dates that i can select in qlikview?

Please help , i am stuck with this.

Re: Date as Column Titles from Excel

rahulsingh12 — Mon, 25 Mar 2019 10:33:00 GMT

Hi,

I tried it but then I get blank field as the date column.

Regards,