Discussion board where members can get started with QlikView.
I'm new in QlikView and i have to develop business application
First, I'm french, then sorry for my english.
At the moment, I have two questions :
I work on an excel file, can i catch the name of the file ( or part of the name ) to use it in a field ?
In my application, user must complete their file at time. To controle this, i have a planning with dates. I'd like to know if its possible for qlikview to write in a field the moment when the information appears for the first time using Reload.
Example : Mike must complete the field [Sales] every 10 of the month. I have a field [ Upload Date ] and when sales is completed, i want to have the date it first appear in qlikview.
I hope you can understand my problem,
Hi Christopher and welcome to the QlikView world,
To your first question, the answer is yes, check the following code using FileName() function:
FileName() AS ExcelFileName
(ooxml, embedded labels, table is Sheet1);
You will get "File.xlsx" in your new field "ExcelFileName". There are some other file name and path functions you can use to get the full path, FilePath(), to the file or the name of the file without extension, FileBaseName().
To the second question, I'm afraid I don't understand what you want to get. Can you post some sample data? Do you want to automatically select the latest date depending on certain criteria?
Hope that helps.
Hi and thanks for your help. Filename works very well.
For the second questions, look my first message and the example.
I want the realoading date in the field when sales have a value for the first time. If you put numbers in sales today and reload, i want the day today. But if tomorow i reload, i still want the day today because sales appears today !
If I understood you right, you can use the function ReloadTime() to fill or create a field with the date when the document has been reloaded:
Date(Floor(ReloadTime())) AS [Date upload in QlikView]
FROM File.xls (....);
Hope that helps.
Ok i used your script but something still miss. See :
if(Sales>=0,Date(Floor(ReloadTime()))) AS [Date upload in QlikView]
(biff, embedded labels, table is Feuil1$);
It works BUT the upload date change each time i reload. the date must not change after the first time ....
I want to tell to qlikview : If there is no data, then write nothing. If its the first time you find a data here, write reload time, else don't change the reload time.
Apply some logic to the calculated date field according to your needs
If(Len(Sales), Date(Floor(ReloadTime()))) AS [Date upload from QlikView]
Len(Sales) return true (greater than zero) if there are some sales. But you need something additional to mark or flag that "else don't change the reload time". How do you want QlikView to know when you have or have not changed your data? Depending on that you will get your conditional. You cannot get QlikView to know whether a cell in an excel spreadsheet has been changed or not, you will need a flag (a field set to 1 or 0 will do), or compare the value you are loading to the same value you already have in memory for what you need to create a unique key per record.
Hope that makes sense.
thanks for Len function, will be useful.
you will need a flag (a field set to 1 or 0 will do), or compare the value you are loading to the same value you already have in memory for what you need to create a unique key per record.
I understand this, and i think i would run, only if I'd know how to do this ....