Loading certain field based on a variable

[shane_spencer]

I've got two scripts that load data from two spreadsheets. Each month someone adds a new column to the spreadsheet with a field name of the month i.e. 01/08/2016 for August. I only want to load a certain month, most like the most recent and I refer to this field a few times in the script:

i.e.

and:

Is there a way of creating a variable so I only have to update the script in one place instead of 6?

[jonas_rezende]

Hi, Shane Spencer.

The content is based on each row of the table? Or is today's date?

Regards,

Jonas Melo

[shane_spencer]

Attached is a sample of the spreadsheet. Hopefully what I explained makes sense in context to that.

[martinpohl · Accepted solution]

define a variable where you set the field name

let vField = '01/02/2016';

then replace the field name within the variable. Because there are a / as delimiter, use brackets [ ]
LOAD System,
     [$(vField)] as Value
FROM yourdata

Regards

[jonas_rezende]

Hi, Shane Spencer.

See the example below.

tmp_Date:

LOAD

[01/06/2016]

FROM

[TableWithNewField];

Let vdate = Peek('[01/06/2016]',0,'tmp_Date');

DROP TABLE tmp_Date;

SAN_Disk:

LEFT KEEP ('Consolidated_BF_Inv')

LOAD

SYSTEM as Configuration_Item,

TIER,

IF(TIER = 'TIER1','$(vdate)') as [Tier1 (GB)],

IF(TIER = 'TIER2','$(vdate)') as [Tier2 (GB)],

'$(vdate)'   as [Disk (GB)]

FROM

R:\Reference_Data\2016 June MI - Usage SAN NAS Disk.xls]

(biff, embedded labels, header 5 lines,  table is [Usage$]);

Where Len('$(vdate)') > 0;

Hope this helps!