Dynamic Format and Tag loader (let only set analysis problem)

[#1]

I am trying to dynamically generate a load statement at runtime given the values in an excel file.  Essentially, the values in the excel file are all of the fields in the application with some 'Metadata' esque values like preferred formatting and additional tags, that get loaded.  The problem I have run into, is that I am trying to load all of the fields and the format codes (which translate into variables).

the basic, non-dynamic result is something like this:

Formats:

load

//Port_Perf_BMPerf (all 3)

$(fdate) as %BUSINESS_DATE_PERF,

$(fdate) as INCEPTION_DATE,

$(fdate) as ORG_INCEPTION_DATE,

$(fdate) as ORG_PERF_INCP_DATE,

$(fdate) as PERF_TERMIN_DATE,

$(fdate) as %BUSINESS_DATE_BMPERF,

$(fdate) as ACCOUNT_INCP_DATE

AutoGenerate(1);

where the variables are:

set f3_dec = Num(1,'#,##0.000;(#,##0.000)');

set f2_dec = Num(1,'#,##0.00;(#,##0.00)');

set fdate = Date(Today(),'MM/DD/YYYY');

Currently, I created this in an include txt file; however, I want to be able to make changes easily, thus store in an Excel file.  I am then creating each of these load statements dynamically with code like the below.  My problem is the red line where I am trying to grab the value in FormatScripts, for each name (there are only 4 distinct values in FormatScripts, while there are 200 fields).  The red line refuses to return a value.  the '%BUSINESS_DATE_PERF' is a hardcoded version of the value that would be returned in vField_Name for testing.  Any suggestions on how best to design this would be appreciated.

fieldList:

LOAD FormatScripts,

     FieldNames

FROM Include\Metadata.xlsx

(ooxml, embedded labels, table is Sheet1);

Format:

load

1 as dummy

AutoGenerate(1);

LET RCount = FieldValueCount('FieldNames');

FOR i=1 to $(RCount)

LET vField_Name = FieldValue('FieldNames',$(i));

let vfield_val = only({<FieldNames={'%BUSINESS_DATE_PERF'}>}FormatScripts);

//Lookup('FormatScripts','FieldNames',$(vField_Name));

//Only({<FieldNames={='$(vField_Name)'}>} FormatScripts);

LET vField_Load = FieldValue('FormatScripts',$(i)) & ' as ' & FieldValue('FieldNames',$(i));

temp_formats:

load

$(vField_Load)

//FieldValue('FormatScripts',$(i)) & ' as ' & FieldValue('FieldNames',$(i))

AutoGenerate(1)

//Resident fieldList

//Where FieldNames = [$(vField_Name)]

;

Outer Join (temp_formats)

LOAD Distinct * Resident Format;

DROP Table temp_formats;

NEXT i;

[hic]

Set analysis cannot be used in the script. You need to do this differently, e.g. by using an If()-function.

HIC

[#3 · Accepted solution]

Okay, so I finally figured out the small tweaks i needed to do, also, needed to use Peek, instead of FieldValue.  Any suggestions on a better way to write this would be appreciated as it has to run through the loop for every field i have, it would be nice to run all of it at once, not sure if I can create an array or something within QV.

FOR i=1 to $(RCount)

LET vField_Name = Peek('FieldNames',i-1,'fieldList');

Let vField_Format = Peek('FormatScripts',i-1,'fieldList');

LET vField_Load = vField_Format & ' as ' & vField_Name;

[#4]

I created a variable that held the entire load statement, then after the loop, loaded the table.