topic Re: Loading certain field based on a variable in QlikView

Loading certain field based on a variable

shane_spencer — Mon, 18 Jul 2016 13:33:15 GMT

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?

Re: Loading certain field based on a variable

jonas_rezende — Mon, 18 Jul 2016 14:13:24 GMT

Hi, Shane Spencer.

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

Regards,

Jonas Melo

Re: Loading certain field based on a variable

shane_spencer — Mon, 18 Jul 2016 14:21:40 GMT

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

Re: Loading certain field based on a variable

martinpohl — Mon, 18 Jul 2016 14:26:26 GMT

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

Re: Loading certain field based on a variable

jonas_rezende — Mon, 18 Jul 2016 15:37:28 GMT

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!