Data Load

[kicchu465]

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

[itec_pao]

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]

Thanks very much Pamuk.

I'll give a try.

[DavidŠtorek · Accepted solution]

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

[Chanty4u]

petter-s‌ may help !

or  marcowedel

[kicchu465]

Hi Storek,

Thanks very much for response.

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

Thanks,

Sk