Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

Date as Column Titles from Excel

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.

9 Replies
Highlighted
MVP
MVP

Re: Date as Column Titles from Excel

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;

Highlighted
Partner
Partner

Re: Date as Column Titles from Excel

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

Highlighted
Creator III
Creator III

Re: Date as Column Titles from Excel

Hi,

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

Kamiel

Highlighted
MVP
MVP

Re: Date as Column Titles from Excel

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;

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Highlighted
Master III
Master III

Re: Date as Column Titles from Excel

Hi

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

Highlighted
Not applicable

Re: Date as Column Titles from Excel

Hi,

You can rename the field names as suggested by  VCanaleContext

Regards,

Sangeetha S

Highlighted
Contributor II
Contributor II

Re: Date as Column Titles from Excel

Thanks, this actually helped it.

Regards,

Keshav

Highlighted
Contributor III
Contributor III

Re: Date as Column Titles from Excel

Hi

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.

TB.JPG

Highlighted
Contributor III
Contributor III

Re: Date as Column Titles from Excel

Hi,

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

Regards,