Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
Hi Raschid,
It's hard without any data or application to test this, but I would do the following (conceptual):
This will solve your problem, the loop will be easier, but it needs some different coding in the beginning.
Jordy
Climber
Thanks, Jordi. I'll try to implement what you said. Hopefully it will work (:
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;
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
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.