Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have a scenario where i read data from the excel spreadsheet which has a date column of format(MMM-YY), but it is interpreting the data present in that column as an integer as [40179] for JAN-10. In This case how do i proceed as to get the desired output in the MMM-YY format.
Regards,
surman
Please find the sample data
Brand Model No of Doors Jan-10 Feb-10 Mar-10 Apr-10 May-10 Honda CIVIC 3 0
1 2 1 1 Honda CR-Z 3 4 4 2 5 5 Honda CIVIC 4 5 5 5 8 2 Honda ACCORD 4 3 3
4 4 3 Hyundai I10 3 0 1 2 3 4 Hyundai I20 3 6 9 13 5 6 Hyundai I40 4 3 4
9 6 9 Hyundai SONATA 4 4 5 8 9 12 Suzuki Alto 4 18 16 14 18 12 Suzuki
Swift 4 9 13 11 18 15
On Fri, Sep 14, 2012 at 12:24 AM, Mayil Vahanan Ramasamy <
Hi
Test:
CrossTable(SalesDate, Sales, 3)
LOAD Brand,
Model,
[No of Doors],
40179,
40210,
40238,
40269,
40299
FROM
[..\My Documents\Downloads\SampleData QlikView.xlsx]
(ooxml, embedded labels, table is Sheet1);
Load *,Date(Sales,'MMM-YY') as TestedDate Resident Test;
Edit:
(or)
Load *,Date(Num#(SalesDate),'MMM-YY') as TestedDate Resident Table;
Drop Table Test;
Hope it helps
Hi,
I had a similar issue and with your suggestion I was able to get desired results.
For some reason when CrossTable feature is used the dates are provided in QV as numbers.
Date(Num#(SalesDate),'MMM-YY') is the way to go.
Thx
Excellent, thanks priyan_pa. I had the same problem as user and your answer solved it for me. So, essentially all I needed was Num#(), then I can use the normal QlikView date functions such as Year(), Date(), etc
hi all..I'm facing the same problem..my dates are like [42644.0] for oct 2016, [42614.0] for sept 2016 and so on..I tried everything written above but nothing worked for me..I guess QV is taking it as text..please help!!
Crosstab:
CrossTable(SalesDate, Sales, 3)
LOAD Brand,
Model,
[No of Doors],
[40179],
[40210],
[40238],
[40269],
[40299]
FROM
(ooxml, embedded labels, table is [Input Data]);
load
*,
Date(SalesDate,'MMM-YY') as Sales_Date
resident Crosstab;
Note: Alter 'MMM-YY' as per need to MM-YY or MM/YYYY
it is not working!!..is there any other way out??
Hi the problem is with data upon loading.
Qlikview is getting it as string.For that you have to use string modification functions like left, right and remove spl characters.
Aftr that use num on tht along with above expression. If you do not get please send me the excel you r using will do it and give you.
Qlik aftr cross table,I think it is getting date as string so use string modification functions..
Sachin
Hi,
create new threads so that other people can help you.
Regards,
Thanks..using string functions made it work!!