Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Loading from Excel files

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

4 Replies
Clever_Anjos
Employee
Employee

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

Not applicable
Author

filename.PNG.png
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?

Gysbert_Wassenaar

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))

));


talk is cheap, supply exceeds demand
Clever_Anjos
Employee
Employee

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

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