topic Re: How to use Unknown column/field name in QlikView

How to use Unknown column/field name

Thu, 31 Dec 2015 16:48:39 GMT

im fetching some columns dynamically, where field names are set to change every month. after filtering the fields need to use a substring/mid function on the fields. tried below code but fetches distinct value

for i = 1 to NoOfFields('Initial')

load

RecNo() as Key,

fieldvalue(fieldname(i, 'Initial'),RecNo()) as

resident

Initial;

next i;

Re: How to use Unknown column/field name

marcus_sommer — Thu, 31 Dec 2015 18:14:20 GMT

I think this Re: Partial rename using field name map will be helpful for you.

- Marcus

Re: How to use Unknown column/field name

stevedark — Sun, 03 Jan 2016 20:21:58 GMT

When loading the Initial table create a fixed key in there, rather than using RecNo on the Resident load. That should give you a row per value, rather than only distinct values. For examples:

LOAD

RecNo() as RowKey,

FROM [ data source details ]

Then use RowKey in place of RecNo in your Loop.

Not entirely sure what you are trying to achieve though. The way you are renaming the field you will only have two fields in the resultant record set; Key and Resident. Is this what you are after?

Just using the * will allow you to have changing field names each month. Is it that you need to rename these to fixed field names after the event?

What you can do is load the first row of the table using a transpose transformation on the table. This will give you a list of all the column names. You can then enumerate around this to create a field rename map (as Marcus_Sommer‌ suggests) or you can build your load statement by concatenating a string to give a statement with the correct AS allocation of fields.

The other thing you can do (if you are loading from a spreadsheet) is to refer to the fields by their location rather than their name and rename them that way.

Hope that helps.

Steve

Re: How to use Unknown column/field name

Mon, 04 Jan 2016 12:59:15 GMT

there will be more than couple of fields in the initial table and field names set to change every month, but will be prefixed with "F_". tried by introducing a RowKey(Key) column but still results in distinct values for columns Fld1,Fld2...

for i = 1 to NoOfFields('Initial')

Temp:

load

Key,

fieldvalue(fieldname($(i), 'Initial'),Key) as Fld$(i)

resident

Initial;

next i;

drop table Initial;

Re: How to use Unknown column/field name

stevedark — Mon, 04 Jan 2016 14:03:08 GMT

I presume you are aware that when you have a list box of any field in QlikView it will only ever show distinct values?

If you add a table box with the Key field and each of the Fld fields do you get duplicate values?

Steve

Re: How to use Unknown column/field name

Mon, 04 Jan 2016 14:16:58 GMT

i'm not yet done with the Model, saw the distinct values from Data model itself

Re: How to use Unknown column/field name

Mon, 04 Jan 2016 15:02:25 GMT

number of fields & field names are set to change each month in source file, so how to use these fields and manipulate applying string functions on it

Re: How to use Unknown column/field name

marcus_sommer — Mon, 04 Jan 2016 15:40:35 GMT

If you have already those informations (old + new field-name) in one table you could simply use a mapping for this like in my above mentioned link. Here those code highlighted on the mapping and rename-statement:

For i = 0 to NoOfTables() - 1

TRACE Looking at table : $(vTableName);

Let vTableName = TableName($(i));

for k = 1 to NoOfFields('$(vTableName)')

Let vFieldName = FieldName($(k),'$(vTableName)');

if Right('$(vFieldName)',8) = '_Current' then

Let vNewFieldName = Left('$(vFieldName)',len('$(vFieldName)')-8) & '_Prior';

FieldNameMap:

Mapping

LOAD

'$(vFieldName)' as FromFieldName,

'$(vNewFieldName)' as ToFieldName

AutoGenerate 1; Resident Initial;

end if;

RENAME Fields using FieldNameMap;

- Marcus

Re: How to use Unknown column/field name

Mon, 04 Jan 2016 15:50:00 GMT

that's fine for renaming the fields, but before that all values are not fetched. distinct values are created on using the mentioned code