6 Replies Latest reply: Oct 6, 2011 3:21 AM by Christophe Brault RSS

    Two new user's questions

    Christophe Brault

      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

        • Re: Two new user's questions
          Miguel Angel Baeyens de Arce

          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

            • Two new user's questions
              Christophe Brault

              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 !

                • Re: Two new user's questions
                  Miguel Angel Baeyens de Arce

                  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

                    • Re: Two new user's questions
                      Christophe Brault

                      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.

                        • Re: Two new user's questions
                          Miguel Angel Baeyens de Arce

                          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