Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

christophebrault
Not applicable

Two new user's questions

Hi,

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 ?

And

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,

Thanks

6 Replies
Miguel_Angel_Baeyens
Not applicable

Re: Two new user's questions

Hi Christopher and welcome to the QlikView world,

To your first question, the answer is yes, check the following code using FileName() function:

TableFromExcel:

LOAD ID,

    FileName() AS ExcelFileName

FROM

C:\File.xlsx

(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.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

christophebrault
Not applicable

Two new user's questions

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 !

Miguel_Angel_Baeyens
Not applicable

Re: Two new user's questions

Hi Christopher,

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:

Table:

LOAD Employes,

     Sales,

     [Date planning],

     Date(Floor(ReloadTime())) AS [Date upload in QlikView]

FROM File.xls (....);

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

christophebrault
Not applicable

Re: Two new user's questions

Ok i used your script but something still miss. See :

Table:

LOAD Employes,

     Sales,

     [Date planning],

     if(Sales>=0,Date(Floor(ReloadTime()))) AS [Date upload in QlikView]

FROM

Example.xls

(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.

Miguel_Angel_Baeyens
Not applicable

Re: Two new user's questions

Hi Christopher,

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.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

christophebrault
Not applicable

Re: Two new user's questions

Hi,

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