Date as Column Titles from Excel

[#1]

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.

[MK_QSL]

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;

[vcanale]

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

[kamielrajaram]

Hi,

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

Kamiel

[jonathandienst]

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;

[sasiparupudi1]

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

[#7]

Hi,

You can rename the field names as suggested by  VCanaleContext

Regards,

Sangeetha S

[keshavkumar]

Thanks, this actually helped it.

Regards,

Keshav

[shalensookdeo]

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.

[rahulsingh12]

Hi,

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

Regards,