topic Re: File Loop with where field found or not found in QlikView

File Loop with where field found or not found

richard24best — Tue, 09 May 2023 12:36:02 GMT

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?

Re: File Loop with where field found or not found

mikaelsc — Tue, 09 May 2023 12:52:18 GMT

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;

Re: File Loop with where field found or not found

marcus_sommer — Wed, 10 May 2023 11:50:27 GMT

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 ...

Re: File Loop with where field found or not found

richard24best — Fri, 12 May 2023 06:31:20 GMT

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