File Loop with where field found or not found

[richard24best]

Hi Team,

I have used a File loop function to load Daily data into Qliksense from Jan 1st 2023, Now we have a situation - 

From May 1st there has been a new field "Project Name" which is present only in the new files after 1st May and doesnt appear in the older files.

Inorder to load data, i am trying the below script in load editor but getting error saying field not found.

if(Date(Date#(subfield( subfield(Filename() , 'MPR_', -1 ), '.xlsx', 1) , 'DD_MM_YYYY'),'DD-MM-YYYY')>=date('05/01/2023','DD-MM-YYYY')," Project Name",'') as Project_Name,

Could you please check and assist me what i am missing above?

[mikaelsc]

start with loading all data in a temp table... then use something like coalesce to fill in the blanks? 

tempAllData: 

load 1 as _justAfieldtocreateatable

autogenerate(1); 

concatenate(tempAllData)

load * from [lib://SourceFolder/*.xls];

FinalTable: 

load 

coalesce(ProjectName,'whatever you want when project field is empty') as Project_Name_Final,

*

resident tempAllData where ... ; 

drop table tempAllData; 

[marcus_sommer · Accepted solution]

It's not possible to query the exists of a field within a load-statement. Beside the suggestion from @mikaelsc to concatenate all loads against a dummy-table and adjusting the results afterwards you may also check the field-names within a pre-load with something like: temp: first 1 load * from Source; or with a check on the filename() or filetime() which is often not difficult within a filelist-loop, for example:

for each file in filelist()
   let vField = if(Date(Date#(subfield( subfield('$(file)' , 'MPR_', -1 ), '.xlsx', 1) , 'DD_MM_YYYY'),'DD-MM-
   YYYY')>=date('05/01/2023','DD-MM-YYYY'), 'Project Name', 'null() as Project Name');
   t: load YourFields, $(vField) from Source;
next

In all cases in which you not specifies the extra fields for all loads directly you will need a concatenation because otherwise the loads would create multiple tables caused from the differing data-structure.

Another method would be not to load from ones from one folder else using for each data-structure an own one folder and applying several load-statements. It's not so silly as it may look like because it keeps the loads itself very simple and readable.

All methods have their benefits and disadvantages ...

[richard24best · Accepted solution]

I created a separate mapping file with key and new field. Did a left join with main table in QLiksense. It worked!!1

Thank you all for your time and support.

Regards,

Richard