Create conditions from information in the fieldname

[#1]

Hej everyone

I am compiling an Excel-sheet with a large number of columns. Clusters of columns have the month number as part of the column name.

M01 corresponds to January, M02 to February etc

I would like to make a field called Month and set all fields that contain M01 to January etc.

Haven't figured out how to do this.

Anyone?

BR

Example:

[CoGS (Loc) M01],
[CoGS (SEK) M01],
[CoGS (Loc) M02],
[CoGS (SEK) M02],
[CoGS (Loc) M03],
[CoGS (SEK) M03],
[Transfer Price (Loc) M01],
[Transfer Price (SEK) M01],
[Transfer Price (Loc) M02],
[Transfer Price (SEK) M02],
[Transfer Price (Loc) M03],
[Transfer Price (SEK) M03],
[Net Sales (Loc) M01],
[Net Sales (SEK) M01],
[Net Sales (Loc) M02],
[Net Sales (SEK) M02],
[Net Sales (Loc) M03],
[Net Sales (SEK) M03],

[#2]

Hi,

you can use in your script something like this:

if(WildMatch(Field,'*M01') = 1, 'Jan', if(WildMatch(Field,'*M02') = 1,'Feb')) as Month

If you want there is "switch case" statement

hope it helps

regards

Giampiero

[#3]

Thanks for the reply.

But I think that WildMatch only compares values IN the field. I need something that checks the name of the field.

[Gysbert_Wassenaar]

You can create a field mapping table and use that to change field names. See this blog post. If you want you can use a mapping table and the mapsubstring() function to help create the field mapping table.

[#5]

Thank you so much for your reply. However I am not very familiar with the mapping functions. I'll try!