Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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;
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
Hi,
You could format your excel file by adding an apostrophe in front of the months 'Jan-2015
Kamiel
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;
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
Thanks, this actually helped it.
Regards,
Keshav
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.
Hi,
I tried it but then I get blank field as the date column.
Regards,