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: 
dries_clairhout
Creator
Creator

Timestamp in Title Excel

Hello everyone,

I got a real brainteaser,

don't even know if it's possible.

A client of mine has a dropfolder on the server where excel files are being dropped. The only thing that could separate the data between files is the timestamp in the title (YYYYMM). You can see the xls files in picture 1.

Now I would love to be able to pick these files up and store them seperately in a qvd with an extra column. In this collumn should be the date stamp. So for example, 3 xls files turn into 3 qvd files and each qvd has an extra collumn where the date from the title is set in.

Any suggestions?1.png

Labels (2)
1 Solution

Accepted Solutions
Vegar
MVP
MVP

Try this:

LOAD 
  *,
  MonthName(Date#(FileBaseName(),'YYYYMM') )as YearMonth,  
  Month(Date#(FileBaseName(),'YYYYMM') )as Month,
  Year(Date#(FileBaseName(),'YYYYMM') )as Year
From 
  LIB:\\Source\*.xls (...) ;

View solution in original post

7 Replies
Vegar
MVP
MVP

What about using filebasename() in your load?

https://help.qlik.com/en-US/sense/June2019/Subsystems/Hub/Content/Sense_Hub/Scripting/FileFunctions/...

LOAD 

*,

FileBaseName() as Date

From *.xls (...) ;

dries_clairhout
Creator
Creator
Author

Hello,

Only problem with this is that each file contains the same fields.

And the date has to be linked to this for example:

Article, Article description are non variable fields that stay the same value trough every excel.

But Stock amount taken, delivered and ordered change every month.

But the titles are all the same.

for example if i select june 2019, I should only see the data from the excel 201906.xlsx

This way I would be able to do month to month comparrisons between the stock.

Hopefully this clears something up.

The ideal scenario would be if I could import the excel, take the title date and add it in a qvd all together...

 

Thanks in advance

Vegar
MVP
MVP

As I understand your description of your scenario and issue my solution will be useful.

When all your files contains exactly the same field names the LOAD * FROM LIB:\\Source\*.xls will automatically concatenate into one single table.

LOAD 

*,

MonthName(Date#(FileBaseName(),'YYYYMM') )as AsOfMonth

From LIB:\\Source\*.xls (...) ;

 

When you select June 2019 you will only get the data from your 201907.xls file.

 

- Vegar

dries_clairhout
Creator
Creator
Author

Hello,

 

I will try this as soon as I can and get back to you.

thanks in advance

dries_clairhout
Creator
Creator
Author

Hello,

 

this seems to work, but I only have one question.

Now it shows up as june 2019, july 2019,....

is it possible to also split the year and month?

thanks in advance.

Vegar
MVP
MVP

Try this:

LOAD 
  *,
  MonthName(Date#(FileBaseName(),'YYYYMM') )as YearMonth,  
  Month(Date#(FileBaseName(),'YYYYMM') )as Month,
  Year(Date#(FileBaseName(),'YYYYMM') )as Year
From 
  LIB:\\Source\*.xls (...) ;
dries_clairhout
Creator
Creator
Author

Thank man,

works like a charm!