Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have and excel dataset that looks like the one below, and would like to load this info into Qlikview. Please help, how do i load the info?
Company | 14 November 2012 | 15 November 2012 | 16 November 2012 | 17 November 2012 | 18 November 2012 | 19 November 2012 | 20 November 2012 | 21 November 2012 |
---|---|---|---|---|---|---|---|---|
Company 1 | 1 | 2 | 2 | 0 | 7 | 2 | 1 | 3 |
Company 2 | 0 | 2 | 1 | 1 | 4 | 3 | 1 | 4 |
Company 3 | 2 | 0 | 1 | 0 | 5 | 4 | 0 | 2 |
Company 4 | 0 | 1 | 0 | 2 | 2 | 0 | 4 | 1 |
Company 5 | 1 | 3 | 1 | 0 | 1 | 4 | 1 | 1 |
Company 6 | 0 | 4 | 2 | 5 | 2 | 1 | 1 | 0 |
Company 7 | 2 | 1 | 3 | 2 | 3 | 1 | 0 | 0 |
Company 8 | 1 | 0 | 1 | 1 | 4 | 0 | 2 | 1 |
Company 9 | 1 | 0 | 2 | 1 | 4 | 0 | 1 | 3 |
Company 10 | 1 | 1 | 0 | 2 | 2 | 0 | 2 | 1 |
2 | 3 | 4 | 5 | |||
Company 2 | 1 | 0 | 1 | 2 | 3 | 0 |
Company 3 | 1 | 2 | 3 | 4 | 5 | 0 |
Company 4 | 1 | 2 | 3 | 4 | 5 | 6 |
Company 5 | 1 | 2 | 3 | 4 | 5 | 0 |
Company 6 | 0 | 0 | 1 | 2 | 3 | 0 |
Company 7 | 1 | 2 | 3 | 4 | 5 | 0 |
Company 8 | 0 | 1 | 2 | 3 | 4 | 5 |
Company 9 | 1 | 2 | 3 | 0 | 0 | 0 |
Company Name | 14 Nov 12 | 15 Nov 12 | 16 Nov 12 | 17 Nov 12 | 18 Nov 12 | 19 Nov 12 |
Company 1 | 0 | 1 | 2 | 3 | 4 | 5 |
Company 2 | 1 | 0 | 1 | 2 | 3 | 0 |
Company 3 | 1 | 2 | 3 | 4 | 5 | 0 |
Company 4 | 1 | 2 | 3 | 4 | 5 | 6 |
Company 5 | 1 | 2 | 3 | 4 | 5 | 0 |
Company 6 | 0 | 0 | 1 | 2 | 3 | 0 |
Company 7 | 1 | 2 | 3 | 4 | 5 | 0 |
Company 8 | 0 | 1 | 2 | 3 | 4 | 5 |
Company 9 | 1 | 2 | 3 | 0 | 0 | 0 |
You can use the CrossTable statement to load this data in a form that's more useful than the layout it now has in excel.
[Data]:
CrossTable(Date, Amount, 1)
LOAD *
FROM
[crosstabtest.xls]
(biff, embedded labels, table is Sheet1$);
If you want to load the data as it is in Excel, then leave out the CrossTable line.
My dates are automitically formatted to numbers ie 14 November 2012 get loaded as 41228. What can i do to ensure that the dates remain as DD Mont Year?
use the Date function: Date(MyDate, 'DD MMMM YYYY')
This is my script,
where do i use the Date function:
SET
ThousandSep=' ';
SET DecimalSep='.';
SET MoneyThousandSep=' ';
SET MoneyDecimalSep=',';
SET MoneyFormat='R # ##0,00;R-# ##0,00';
SET TimeFormat='hh:mm:ss TT';
SET DateFormat='YYYY/MM/DD';
SET TimestampFormat='YYYY/MM/DD hh:mm:ss[.fff] TT';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
CrossTable(Date, Amount, 1)
LOAD *
FROM
[..\Daily Safety Report (4).xlsx]
(ooxml, embedded labels);