Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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?
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))
));
subfield(filebasename(),'-',3) will return Year
subfield(filebasename(),'-',4) will return Month