Change the name of the columns. Large number of tables

[fetoss88]

Hello. There are many qvd files in the folder. I need to rename the columns in each of the files in the format 'Field' & '_' & 'Table Name'

I wrote a script but it doesn't work. When loading the second table, it stops working. What to change?

TABLES:
LOAD
FileName
FROM
[C:\Users\analitik\Desktop\В разработке\Qlik_test\100001.load_ERP\Т.xlsx]
(ooxml, embedded labels, table is Sheet1);

FOR i=1 to NoOfRows('TABLES')

LET vTableName = Peek('FileName',$(i)-1,'TABLES');

[$(vTableName)]:
LOAD
*
FROM C:\Users\analitik\Desktop\В разработке\Qlik_test\100001.load_ERP\QVDs\$(vTableName).qvd (qvd);

FOR g = 1 to NoOfFields('$(vTableName)')

let FieldOld=FieldName('$(g)','$(vTableName)');

let FiledNew='$(FieldOld)'&'_'&'$(vTableName)';

Rename Field $(FieldOld) to $(FiledNew);

;

NEXT g

IF NoOfRows('$(vTableName)')>1 then
STORE [$(vTableName)] into 'QVDs1\$(vTableName).qvd' (qvd);
ENDIF

DROP Table [$(vTableName)];

NEXT i

DROP Table TABLES;

[MayilVahanan]

Hi @fetoss88 

Based on ur logic, If you're qvd has only one record, then it won't store into qvd.

IF NoOfRows('$(vTableName)')>=1 then
STORE [$(vTableName)] into 'QVDs1\$(vTableName).qvd' (qvd);
ENDIF

And also, you can use Trace comment or enable Generate Log file in Document properties will help you to debug easier. 

[marcus_sommer · Accepted solution]

As far as your fields and/or table contain any space or special char the fieldnames need to be wrapped like: "Field with Space" or [Field-with-special-chars] and your rename-statement is missing it.

Further are there special reasons why you apply here an own logic and not using the inbuilt QUALIFY statement? Also why applying it to extra qvd-data and not while loading the data within a final data-model?

Beside this I suggest to rethink the whole approach because such qualifying caused usually much more trouble within the datamodel and the UI as that it would be helpful. Especially not to prevent unwanted associations between tables which should be always enabled/disabled explicitly because otherwise there would be no control which data are really loaded. If the aim is to track from where the field is coming and which content it has you could add an extra source-field to your tables and/or adding comments and tags to the fields and tables.

- Marcus  

[fetoss88]

Your advice was very helpful. Thanks you.👍

[fetoss88]

Thank you for your advice, they helped me a lot.