Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

kicchu465
New Contributor III

Data Load

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


1 Solution

Accepted Solutions
DavidŠtorek
Contributor III

Re: Data Load

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

5 Replies
itec_pao
Contributor

Re: Data Load

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

kicchu465
New Contributor III

Re: Data Load

Thanks very much Pamuk.

I'll give a try.

DavidŠtorek
Contributor III

Re: Data Load

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

sureshqv
Esteemed Contributor III

Re: Data Load

petter-s‌ may help !

or  marcowedel

kicchu465
New Contributor III

Re: Data Load

Hi Storek,

Thanks very much for response.

I have tried with above logic and it is working fine.

Thanks,

Sk