Change column name in the load script based on conditions

[ElviraD]

Hi everyone!

I have a question regarding changing column names in the load script. I want to go iteratively through all the columns (loaded as Raw_Data first) and change column names in Final_Data based on the content of the FieldName. 

E.g. if FieldNames contains  '%question%', change the FieldName to 'Question', and if the FieldName contains '%answer%' change the Name to 'Answer'

Something like:

For i=1 to NoOfFields('Raw_Data').

Final_Data:

load
=if(FieldName(i)like'%question%', 'Question', false)

=if(FieldName(i)like'%question%', 'Answer', false)

NEXT i

I have 2 questions:

1) How to iterate through column names?

2) How to create a nested IF clause?

Thank you in advance for your help!

[Taoufiq_Zarra]

you want to change Column Name or Row data ?

can you share a sample data ?

[ElviraD]

Hi Taoufiq!

I want to change only column names and leave the data the way it is. My problem is that I will not know the number and order of columns, that is why I want to change the names in a loop. 

Respondant_id	This is the Question-1	This is the Question-2	This is the Question-10	This is the Answer-1	This is the Answer-2	This is the Answer-10

and I want the final table to look like this:

Respondant_id	Question-1	Question-2	Question-10	Answer-1	Answer-2	Answer-10

based on the content (e.g. if it contains the word 'Question', use a substring of the old FieldName as a new FieldName.

Thank you for your help!

[Taoufiq_Zarra · Accepted solution]

I'm very proud of myself 😂😂😁

befor :

Script:

Data:
load * inline [
    Respondant_id, This is the Question-1, This is the Question-2, This is the Question-10, This is the Answer-1, This is the Answer-2, This is the Answer-10
    a ,v  ,  b,  c,f  ,  h,  f

];

FOR i = 1 to NoOfFields('Data')

let FieldOld=FieldName($(i),'Data');
let FiledNew=if(wildmatch(Upper('$(FieldOld)'),'*QUESTION*')>0,'Question'&KeepChar('$(FieldOld)',0123456789),if(wildmatch(upper('$(FieldOld)'),'*ANSWER*')>0,'Answer'&KeepChar('$(FieldOld)',0123456789),'$(FieldOld)'));
Rename Field '$(FieldOld)' to '$(FiledNew)';

;

NEXT i

After :

[ElviraD]

Thank you, Taoufiq! It worked like a charm! 🙂