Renaming columns name within Load script

[Sharbel]

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

[Chanty4u]

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;

[Jaggu]

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;

[Or]

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

[Sharbel]

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

[marcus_sommer]

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

[Sharbel]

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?

[Kushal_Chawda · Accepted solution]

@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;

NEXT

RENAME Fields using Rename_Fields;

[Sharbel]

works perfect @Kushal_Chawda 

thanks a lot