5 Replies Latest reply: Apr 18, 2018 8:22 AM by SK B RSS

    Data Load

    SK B

      Hi all,

       

      I need your help in loading the data into Qliksense. We will be receiving the file names as below.

       

      02042018_File

      03012018_File

      05022018_File

      05032018_File

      07122017_File

      09042018_File

      11012018_File

      12022018_File

      12032018_File

      14122017_File

      15012018_File

      19032018_File

      21122017_File

      22012018_File

       

      Logic which I have applied to load the data as below. In the below logic Every time when I run the application it will check each and every file and then it will append new and Updated records but I need to implement logic in such way that I  just need to check only the changed files and new added files and then append the records. Could you please help me in getting this done.

       

      //First Load


      If(FileSize('[Intial.qvd]')=0) THEN


      [Table 1]:

      LOAD

            Loading the data

      STORE data into

      Drop Table ;

      else


      Max_Date:

      LOAD

          Max(Num(date)) as MaxDate

      FROM [Intial.qvd]

      (qvd);


      Let MaxDate= peek('MaxDate',0,'Max_Date');



      [Table 1]:


      LOAD

           Loading the data

      FROM [path*File*.xlsx]

      where "Date">= '$MaxDate)' or  "Valid from"< '$(MaxDate)';



      Concatenate

      LOAD

      data

      FROM [Initial.qvd]

      (qvd)

      where not exists(Key);


      Thanks

      S k


        • Re: Data Load
          Oktay Pamuk

          Hi,

           

          The one question is, how would you want to check whether a file has been changed (I assume the content), which has been delivered previously?

           

          Other than that you can include the filename by using filebasename() as filename, for each record. Then you read in the existing ones first (from QVD), and read only the records "where not exists(filename,filebasename() ).

           

          For the files which has been changed, you have to include additional function called filetime(), which would look at the creation time of the file. Then you can have a field called filebase()&'_'&filetime() as FileNameTime and when reading in new files you use the condition "where not exists(FileNameTime,filebasename() &'_'&filetime()).

           

          regards,


          Oktay

          • Re: Data Load
            David Štorek

            Hi I am not realy sure if i understand what you are up to, but check this if it will help...

             

            Max_Date:

            LOAD

                Max(Num(date)) as MaxDate

            FROM [Intial.qvd]

            (qvd);


            Let MaxDate= peek('MaxDate',0,'Max_Date');

             

            FOR Each File in FileList ('lib://.../*.xlsx');

             

                LET FileDate = num(FileTime('$(file)'));

             

                IF $(FileDate)>$(MaxDate) THEN

                  

                    MyTable:

                    LOAD *

                      From $(File);

                ENDIF

             

            NEXT File;

            This part makes sure it loads only new or changed file. I dont know where does your "date" field come from.

             

            Hopes it help

            • Re: Data Load
              Chanty 4u

              petter-s may help !

               

              or  Marco Wedel