How to use Unknown column/field name

[#1]

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;

[marcus_sommer]

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

- Marcus

[stevedark]

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

[#4]

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;

[stevedark]

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

[#6]

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

[#7]

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

[marcus_sommer]

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;

  NEXT

Next

RENAME Fields using FieldNameMap;

- Marcus

[#9]

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