topic Re: How to change field name with a for loop in QlikView

How to change field name with a for loop

Fri, 10 Jan 2014 10:43:34 GMT

Hi,

How can I make this simpler with a for loop? Can I make a loop inside the field name? The fields come from a previously loaded table.

SecondTable:

LOAD *,

if (isnull([test]),0,[data 2013]) as [data 2013 new],

if (isnull([test]),0,[data bis 2013]) as [data bis 2013 new],

if (isnull([test]),0,[data 2014])as [data 2014 new],

if (isnull([test]),0,[data bis 2014]) as [data bis 2014 new],

if (isnull([test]),0,[data 2015])as [data 2015 new],

if (isnull([test]),0,[data bis 2015]) as [data bis 2015 new],

if (isnull([test]),0,[data 2016])as [data 2016 new],

if (isnull([test]),0,[data bis 2016]) as [data bis 2016 new],

if (isnull([test]),0,[data 2017])as [data 2017 new],

if (isnull([test]),0,[data bis 2017]) as [data bis 2017 new],

if (isnull([test]),0,[data 2018])as [data 2018 new],

if (isnull([test]),0,[data bis 2018]) as [data bis 2018 new]

Resident FirstTable;

Thanks for your help!

Re: How to change field name with a for loop

jagan — Fri, 10 Jan 2014 10:51:50 GMT

Hi,

Try using this from Qlikview Help file

Map ... using

The map ... using statement is used for mapping a certain field value or expression to the values of a specific mapping table. The mapping table is created through the Mapping statement.

The automatic mapping is done for fields loaded after the map ... using statement until the end of the script or until an unmap statement is encountered.

The mapping is done last in the chain of events leading up to the field being stored in the internal table in QlikView. This means that mapping is not done every time a field name is encountered as part of an expression, but rather when the value is stored under the field name in the internal table. If mapping on the expression level is required, the Applymap() function has to be used instead.

The syntax is:

map *fieldlist using mapname

*fieldlist is a comma separated list of the fields that should be mapped from this point in the script. Using * as field list indicates all fields. The wildcard characters * and ? are allowed in field names. Quoting of field names may be necessary when wildcards are used.

mapname is the name of a mapping table previously read in a mapping load or mapping select statement.

Examples:

map Country using Cmap;	Enables mapping of field Country using map Cmap.
map A, B, C using X;	Enables mapping of fields A, B and C using map X.
map * using GenMap;	Enables mapping of all fields using GenMap.

Regards,

Jagan.

Re: How to change field name with a for loop

jonathandienst — Fri, 10 Jan 2014 11:00:22 GMT

I think Jagan is thinking of this - which might be what you need:

FieldMap:

Mapping SQL SELECT oldnames, newnames FROM datadictionary;

Rename fields using FieldMap;

(also from the manual/help file)

HTH

Jonathan

Re: How to change field name with a for loop

jagan — Fri, 10 Jan 2014 11:07:47 GMT

HI,

Try this, the previous post is for mapping field values. This is for renaming field valus. You can also check this in Qlikview Help file.

Using the Rename Fields Statement

This is a very good method if you want to rename all or some fields at the end of the script run. The best way is to use a mapping table with the old and new field names and use this as follows.

Example:

FieldNameMap;

Mapping Load OldFieldName, NewFieldName From FieldNames ;

Rename Fields using FieldNameMap;

You can store the mapping table in your database or in an Excel sheet so that it is easier to maintain.

A good solution may be to use a combination of using the as specifier and using the Rename fields statement. The as qualifier is used to define the data model, and the Rename fields statement to make the fields user-friendly.

Regards,

Jagan.