topic Re: load existing field in table in subroutine in QlikView

load existing field in table in subroutine

Anonymous — Mon, 21 Dec 2015 22:32:37 GMT

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.

file1
invoice number	amount
a	1
b	2
c	3
d	4

file2
invoice number	Money
e	12
f	13
g	14
h	15

Any help is much appreciated.

Re: load existing field in table in subroutine

swuehl — Mon, 21 Dec 2015 22:51:31 GMT

Maybe just load your tables, then finally do rename Money field to 'Invoice amount'

LOAD * from ...;

RENAME FIELD Money TO [Invoice amount];

Re: load existing field in table in subroutine

effinty2112 — Mon, 21 Dec 2015 23:10:20 GMT

Hi,

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

Data:

LOAD A as [invoice number],

B as amount

FROM

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);

Re: load existing field in table in subroutine

Anonymous — Tue, 22 Dec 2015 14:33:35 GMT

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.