topic Re: Loading from Excel files in QlikView

Loading from Excel files

Thu, 24 Oct 2013 12:52:59 GMT

Dear all,

Each month I'm loading from a new excel file. I'm using the following dirty "formula"

Load
*
FROM
[..\Data\status-net*]

load
club as Club,
     dep,
     'actual' as Scenerio,
    Left(Measure_Date, Index(Measure_Date,' ', SubStringCount(Measure_Date, ' ')-1)-1) as Measure,
     Date(Date#(Left(TextBetween(Measure_Date,' ', ' ', (SubStringCount(Measure_Date,' ')-1)),3) & Right(Measure_Date, 4),'MMMYYYY'),'DD.MM.YYYY') as Datee,
     Value,
     'primaryClubVisits' as Source,
     filename() as FilenameFakta

Resident Temp;
DROP Table Temp;

The Excel file contains values from this year and the last year. The heading in the Excel file are the same except that the heading changing from "year" for each year and monthly for each month. I'm using the headings to make a date, as you can see from the script. That's why I am using a temp file to load everything (using the load *). Otherwise I will not be able to load the changing headings.

The problem starts when I will receive files in 2014 when the headings contains 2013 and 2014. Meaning that I will have duplicated values for 2013 when we are in 2014. And duplicated values when we are 2015 due to the 2014 values.

Any suggestion?

Thanks

Re: Loading from Excel files

Clever_Anjos — Thu, 24 Oct 2013 13:05:27 GMT

Maybe you should rewrite your expressions using "SubField"

Left(Measure_Date, Index(Measure_Date,' ', SubStringCount(Measure_Date, ' ')-1)-1) as Measure

Date(Date#(Left(TextBetween(Measure_Date,' ', ' ', (SubStringCount(Measure_Date,' ')-1)),3) & Right(Measure_Date, 4),'MMMYYYY'),'DD.MM.YYYY') as Datee,

turns

subfield(Measure_Date,' ',1) as Measure

date(date#(Subfield(Measure_Date,' ',2) & Subfield(Measure_Date,' ',3),'MMMYYYY'),'MMM/YYYY') as Datee

Re: Loading from Excel files

Thu, 24 Oct 2013 13:25:41 GMT

Sorry but that did not work, returning the same values. I also have a filename function, maybe that one can be used in some way?

Re: Loading from Excel files

Gysbert_Wassenaar — Thu, 24 Oct 2013 13:56:15 GMT

The obvious solution is to make sure the excel files themselves don't include the year and month number in the headers. Clean up the thrash at the source. Probably won't happen. So, assuming the columns are at least always in the same place, try loading with No Labels and skip the first row. Then rename the fields yourself:

Load

@1 as Dim1,

@2 as Dim2,

@3 as Year,
@4 as Month,
@5 as Amount

From *.xls (biff, no labels, table is Sheet1$, filters(

Remove(Row, Pos(Top, 1))

));

Re: Loading from Excel files

Clever_Anjos — Thu, 24 Oct 2013 14:14:39 GMT

subfield(filebasename(),'-',3) will return Year

subfield(filebasename(),'-',4) will return Month