I have two excel files and these have field names invoice number and Invoice amount. Invoice amount is termed as amount in one file and Money in another file. I have written a subroutine to load both the excel files in qlikview and I want to specify "if field name is amount then load amount otherwise look for Money and load the existing field as Invoice amount". One file can have either field name amount or Money. I am not sure which function should be used in load script. FieldNumber function can't be used as it must not reference the table currently being loaded. I don't want to use ScriptError function.
This script will load both files creating the fieldnames [invoice number] and amount in the resulting resident table. The header is 1 lines qualifier ignores the excel headers and allows you to specify the headers you want for the two fields
LOAD A as [invoice number],
B as amount
file1.xlsx // amend filename here or replace with *.xlsx to load both files from common directory
(ooxml, no labels, header is 1 lines, table is Sheet1);
Andrew- Unfortunately, the number of columns/fields are not the same and not in the same order in all the excel files so we can not use alpha/numeric (A / @1 as field name) references of field names. My bad, I should have specified this constraint.