topic Re: Renaming columns name within Load script in App Development

Renaming columns name within Load script

Sharbel — Mon, 15 May 2023 10:57:47 GMT

Hi,

I need help with Qlik sense script that loads data from a data source (excel file) and also renames the columns names in a manner that it removes all blank strings within column name .

example for source excel file:

the desired outcome would look like:

Any help would be appreciable

Sharbel

Re: Renaming columns name within Load script

Chanty4u — Mon, 15 May 2023 11:28:49 GMT

Try this

LOAD

[Column 1] as Column1,

[Column 2] as Column2,

[Column 3] as Column3,

...

[Column N] as ColumnN

FROM [Path\to\your\Excel\File.xlsx]

(ooxml, embedded labels, table is Sheet1);

// Rename columns by removing blank strings

FOR i = 1 TO NoOfFields('Table1')

LET vFieldName = FieldName('Table1', i);

LET vNewFieldName = Replace(vFieldName, ' ', '');

RENAME FIELD $(vFieldName) AS $(vNewFiel

dName);

NEXT;

Re: Renaming columns name within Load script

Jaggu — Mon, 15 May 2023 11:30:25 GMT

Hi Please refer the below code . For the Mapping table T1 please create a required format in excel and load to it.

T1:
mapping Load * inline [

Field1 , Field2
Emp , emp
Start Date , Startdate
agr 1997 etc , agr1997etc
];

T2:
Load * inline [
Emp , Start Date , agr 1997 etc
A, , 12/2/2023 , 45
B, 12/2/2023 , 45

];

Rename Fields using T1;

Re: Renaming columns name within Load script

Or — Mon, 15 May 2023 12:30:32 GMT

Just a quick add-on to this one - it can crash if you already have field names without the spaces due to other loads. It'd probably be a good idea to add a check if the new field name is available before renaming, using something like FieldNumber($(vNewFieldName),'Table1')=0

Re: Renaming columns name within Load script

Sharbel — Tue, 16 May 2023 12:31:55 GMT

hello,

i wrote the following script:

tmp:

LOAD

emp,

start date,

agr 1997 etc

FROM [excel file];

FOR i = 1 TO NoOfFields('tmp')

LET vFieldName = FieldName($(i),'tmp');

Next i

LET vNewFieldName = Replace('$(vFieldName)',' ', '');

tmp1:

Load

$(vNewFieldName)

Resident tmp;

Drop Table tmp;

but still got an error indicating that the field startdate not found.

the error occurred when loading tmp1...

any ideas how to fix the script?

sharbel

Re: Renaming columns name within Load script

marcus_sommer — Wed, 17 May 2023 11:09:12 GMT

You missed the rename-statement. Take a closer look on the suggestion from @Chanty4u.

Re: Renaming columns name within Load script

Sharbel — Wed, 17 May 2023 16:57:50 GMT

I modified the script as suggested by @Chanty4u by adding Rename Field $(vFieldName) to $(vNewFieldName);

here is the full script :

table:
Load * inline [
"emp no" , "start date" , "agr 1997 etc"
100,17/05/2023,1500
200,18/12/2019,1250

];

FOR i = 1 TO NoOfFields('table')

LET vFieldName = FieldName($(i),'table');

Next i

LET vNewFieldName = Replace('$(vFieldName)',' ', '');

Rename Field $(vFieldName) to $(vNewFieldName);

table1:
Load
$(vNewFieldName)
Resident table;
Drop Table table;

still when tried to run the above script i got the following error:

Any ideas on how to fix it?

Re: Renaming columns name within Load script

Kushal_Chawda — Wed, 17 May 2023 22:45:40 GMT

@Sharbel try below

table:
LOAD "emp no" ,
"start date" ,
"agr 1997 etc"
FROM table;

for i=1 to NoOfFields('table')

Rename_Fields:
mapping LOAD FieldName($(i),'table') as FieldName_From,
replace(FieldName($(i),'table'),' ','') as FieldName_To
AutoGenerate 1;

RENAME Fields using Rename_Fields;

Re: Renaming columns name within Load script

Sharbel — Thu, 18 May 2023 05:34:24 GMT

works perfect @Kushal_Chawda

thanks a lot