Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
raschid_m
Contributor II
Contributor II

Loading the missing files

 

Dear Qlik Sense Users,

 

        I've a question  regarding my script, which you can see below. The purpose of the piece of code is basically to check the folder for the new files, compare their names to the ones, which are stored in field called "filename", which was loaded initially from the QVD file. If filename is not mentioned there,the the file must be loaded, simple incremental load. But for some reason compiler turns blind eye on anything that goes after if statement, as it's evident from the second picture.

       Unfortunately, I cannot load any fragment of data due to its confidential nature. Could you please advice anything to change in code ?

      With kind regards

      Raschid

 

 

2019-11-04_15h32_14.png

 

2019-11-04_15h42_56.png

1 Solution

Accepted Solutions
raschid_m
Contributor II
Contributor II
Author

   So, finally i managed to solve the problem. Thanks to Jordy for the idea, which led me to this solution.  Thus I want to share my script, hoping that people with similar task won't struggle much (:

 

// This Script is to be used only in case when some file was not loaded previously for the historical tracking
//
// ------------------------------------- Getting the names of all the files --------------------------------------------//

For Each File in FileList('lib://Doors-FileShare (de_umr7fe)/Metrics_JLR_BSW_*.xlsx')
let file_name = subfield('$(File)','/',-1);
trace FileName('$(File)');
FileList:
Load '$(file_name)' as filename

AutoGenerate 1;

Next File

NoConcatenate

//------------------------------------- Getting the filenames, that are already recorded -------------------------------//

Historical_Data:

LOAD filename
From [lib://Doors-FileShare (de_umr7fe)/HistoricalData_Doors_Test_3.qvd] (qvd);

//------------------------------------ Loading all the common files from two tables -----------------------------------//

Intersection:
LOAD filename as All_files
Resident FileList;
Inner Join
LOAD filename as All_files
Resident Historical_Data;

//------------------------------------ then loading all the unique files from each table ------------------------------//

Complement:
Load filename as Missing_files
Resident FileList
WHERE NOT Exists(All_files,filename);
Concatenate
LOAD filename as Missing_files
RESIDENT Historical_Data
WHERE NOT Exists(All_files,filename);

NoConcatenate

//------------------------------------ Saving the missing filenames ---------------------------------------------------//

Missing:

LOAD
Missing_files
Resident Complement;

Store Missing into [lib://Doors-FileShare (de_umr7fe)/Missing_Files.qvd] (qvd);

Drop Tables FileList, Historical_Data, Intersection, Complement, Missing;

Missing_Data:

SET vPath = 'lib://Doors-FileShare (de_umr7fe)/';

LOAD *

FROM [lib://Doors-FileShare (de_umr7fe)/Missing_Files.qvd] (qvd);

//----------------------------------------- Loading the missing files ----------------------------------------------------//

For Each Value in FieldValueList('Missing_files')

Let FileName = vPath&'$(Value)';
Trace $(FileName);
Missing_Info:
LOAD
timestamp(timestamp#(mid(FileName('$(FileName)'),17,16),'YYYY.MM.DD_hh.mm'),'YYYY.MM.DD hh:mm') as timestamp,
'$(Value)' as filename,
*
From [$(FileName)] (ooxml, embedded labels, table is [All Requirements]);

Next Value

//---------------------------------------- Saving the data from the files ----------------------------------------------- //

Store Missing_Info into [lib://Doors-FileShare (de_umr7fe)/Missing_Doors_Data.qvd] (qvd);

Drop Tables Missing_Data, Missing_Info;

// ------------------------------- Concatenating the missing data with the exisitng one ----------------------------------//

Stitching:

LOAD
*
FROM [lib://Doors-FileShare (de_umr7fe)/HistoricalData_Doors_Test_3.qvd] (qvd);

Concatenate(Stitching)

LOAD
*
FROM [lib://Doors-FileShare (de_umr7fe)/Missing_Doors_Data.qvd] (qvd);

Store Stitching into [lib://Doors-FileShare (de_umr7fe)/Complete_Doors_Data.qvd] (qvd);

exit script;

View solution in original post

5 Replies
JordyWegman
Partner - Master
Partner - Master

Hi Raschid,

It's hard without any data or application to test this, but I would do the following (conceptual):

  1. Load all the different file names from your QVD
  2. Load all the files from the folder
  3. Do a not exists on the field and keep only the fields that are not there. Call this field [ToBeAdded]
  4. Then Create the loop with: For Each File in FieldValueList([ToBeAdded]) and then concatenate the files in this loop to the QVD and store the QVD.

This will solve your problem, the loop will be easier, but it needs some different coding in the beginning.

Jordy

Climber

Work smarter, not harder
raschid_m
Contributor II
Contributor II
Author

Thanks,  Jordi. I'll try to implement what you said. Hopefully it will work (:

raschid_m
Contributor II
Contributor II
Author

   So, finally i managed to solve the problem. Thanks to Jordy for the idea, which led me to this solution.  Thus I want to share my script, hoping that people with similar task won't struggle much (:

 

// This Script is to be used only in case when some file was not loaded previously for the historical tracking
//
// ------------------------------------- Getting the names of all the files --------------------------------------------//

For Each File in FileList('lib://Doors-FileShare (de_umr7fe)/Metrics_JLR_BSW_*.xlsx')
let file_name = subfield('$(File)','/',-1);
trace FileName('$(File)');
FileList:
Load '$(file_name)' as filename

AutoGenerate 1;

Next File

NoConcatenate

//------------------------------------- Getting the filenames, that are already recorded -------------------------------//

Historical_Data:

LOAD filename
From [lib://Doors-FileShare (de_umr7fe)/HistoricalData_Doors_Test_3.qvd] (qvd);

//------------------------------------ Loading all the common files from two tables -----------------------------------//

Intersection:
LOAD filename as All_files
Resident FileList;
Inner Join
LOAD filename as All_files
Resident Historical_Data;

//------------------------------------ then loading all the unique files from each table ------------------------------//

Complement:
Load filename as Missing_files
Resident FileList
WHERE NOT Exists(All_files,filename);
Concatenate
LOAD filename as Missing_files
RESIDENT Historical_Data
WHERE NOT Exists(All_files,filename);

NoConcatenate

//------------------------------------ Saving the missing filenames ---------------------------------------------------//

Missing:

LOAD
Missing_files
Resident Complement;

Store Missing into [lib://Doors-FileShare (de_umr7fe)/Missing_Files.qvd] (qvd);

Drop Tables FileList, Historical_Data, Intersection, Complement, Missing;

Missing_Data:

SET vPath = 'lib://Doors-FileShare (de_umr7fe)/';

LOAD *

FROM [lib://Doors-FileShare (de_umr7fe)/Missing_Files.qvd] (qvd);

//----------------------------------------- Loading the missing files ----------------------------------------------------//

For Each Value in FieldValueList('Missing_files')

Let FileName = vPath&'$(Value)';
Trace $(FileName);
Missing_Info:
LOAD
timestamp(timestamp#(mid(FileName('$(FileName)'),17,16),'YYYY.MM.DD_hh.mm'),'YYYY.MM.DD hh:mm') as timestamp,
'$(Value)' as filename,
*
From [$(FileName)] (ooxml, embedded labels, table is [All Requirements]);

Next Value

//---------------------------------------- Saving the data from the files ----------------------------------------------- //

Store Missing_Info into [lib://Doors-FileShare (de_umr7fe)/Missing_Doors_Data.qvd] (qvd);

Drop Tables Missing_Data, Missing_Info;

// ------------------------------- Concatenating the missing data with the exisitng one ----------------------------------//

Stitching:

LOAD
*
FROM [lib://Doors-FileShare (de_umr7fe)/HistoricalData_Doors_Test_3.qvd] (qvd);

Concatenate(Stitching)

LOAD
*
FROM [lib://Doors-FileShare (de_umr7fe)/Missing_Doors_Data.qvd] (qvd);

Store Stitching into [lib://Doors-FileShare (de_umr7fe)/Complete_Doors_Data.qvd] (qvd);

exit script;

JordyWegman
Partner - Master
Partner - Master

Nice one Rachid! I think it can be a bit more optimized if you don't save the missing files, because you can put them in a separate table and later call that again with a resident load. But after all, good solution!

Jordy

Climber

Work smarter, not harder
raschid_m
Contributor II
Contributor II
Author

Yes, you're right. Those saving steps are abundant and can be simplified. I just made them at first run to check if everything runs as intended.