Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
hopkinsc
Partner
Partner

remove ## from field names

Hi,

I have a text file that has multiple fields, the field names are all in the following format

##Field1##

##Field2##

##Field3##

etc

i want to rename the fields and remove the ## from the names.

so i would end up with

Field1

Field2

Field3

etc

i can rename manually by using..

##Field1## as Field1

etc

but i have 100's of files like this, all with different field names.

is there a way i can loop through each of my files and just remove the # character from the field names? note, thats its only trhe field names i need, not the field values..

thanks

14 Replies
sunny_talwar

See if this helps:

Table:

LOAD Concat(PurgeChar(@1, '#'), '|', Order) as List;

LOAD @1,

  RecNo() as Order

FROM

Test.xlsx

(ooxml, explicit labels, table is Sheet1, filters(

Transpose()

));

LET vList = Chr(39) & Peek('List') & Chr(39);

DROP Table Table;

FinalTable:

LOAD *

FROM

Test.xlsx

(ooxml, no labels, header is 1 lines, table is Sheet1);

For i = 1 to NoOfFields('FinalTable')

  LET vFieldOld = '[' & FieldName($(i), 'FinalTable') & ']';

  LET vFieldNew = '[' & SubField($(vList), '|', $(i)) & ']';

  RENAME Field $(vFieldOld) to $(vFieldNew);

NEXT i;

UPDATE: Attaching a sample

balrajahlawat
Champion
Champion

Peter_Cammaert
Partner
Partner

This may sound like a lot of work (and it is at first) but there is no easy way. The following is pseudocode. Should be easy to complete (look at the help on the different QvdXXXX functions)

For every text file do the following

  Load your text file into memory as-is.

  Store the text file into a QVD

  Drop the internal table;

  Set vLOADStmt = LOAD ;

  For i = 1 to QvdNoOffields() do the following

    Let vLOADStmt = '$(vLOADStmt), [' & QvdFieldName( ) & '] AS [' &

                    replace(QvdFieldName(), '##', '') & ']';

  Next

  TempTable:

  LOAD $(vLOADStmt) FROM [QvdPath] (qvd);

  STORE TempTable into [TextPath] (txt);

  DROP Table Temp Table;

Next

Best,

Peter

pokassov
Specialist
Specialist

Hello!

//--- load empty table

t1:

LOAD *

FROM

(txt, unicode, embedded labels, delimiter is '\t', msq, filters(

Remove(Row, RowCnd(Interval, Pos(Top, 2), Pos(Bottom, 1), Select(1, 0)))

));

//--- create a field's list

let vSQL='';

FOR i=1 to NoOfFields('t1')

let vSQL=vSQL&Chr(10)&' '&FieldName(i,'t1')&' As '&replace(FieldName(i,'t1'),'#','')&',';

NEXT i;

LET vSQL=Left(vSQL, Len(vSQL)-1);

DROP Table t1;

//--- load data with new fields

t1:

LOAD

  $(vSQL)

FROM

(txt, unicode, embedded labels, delimiter is '\t', msq);

hopkinsc
Partner
Partner
Author

hi Sunny T.

i think this is what i want but i cant seem to get it working with my data, see attached sample.

can you see what i am doing wrong?

marcus_malinow
Specialist III
Specialist III

Here's a script which will use mappings to rename fields after they have been loaded.

It will loop through all your tables (a), then all the fields (b), creating mapping tables for each of your source tables, then renaming your fields.

If you really wanted to you could do this in a single mapping but you would have to load distinct field names.

for a = 1 to NoOfTables()
for b = 1 to NoOfFields(TableName($(a)-1))
_MAPPING
$(a):
LOAD
FieldName($(b), TableName($(a)-1)) as fieldnametomap
AUTOGENERATE 1;

next b

MAPPING
$(a):
MAPPING LOAD
fieldnametomap,
replace(fieldnametomap, '##', '')
RESIDENT _MAPPING$(a);

DROP TABLE _MAPPING$(a);


RENAME FIELDS USING MAPPING$(a);
next a

tamilarasu
Champion
Champion

Did you try what I have suggested in your same thread.

LOOP THROUGH FILES IN FOLDER AND REMOVE CHARACTERS FROM FIELD NAMES AND DATA

hopkinsc
Partner
Partner
Author

Hi, i didnt because you are hard coding the field names. i cant do that as all of the field names are different in each of the file.s

sunny_talwar

This is as far as long as I am able to get. I think the header row and the data rows format is not consistent and causing issue. Is this the final version that you have? If you can look into the format and see if it isn't the actual one, this might still work.

Capture.PNG