Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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 ...
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
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;
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 ...
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