remove ## from field names

[hopkinsc]

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

[Gabriel]

Hi,

I think if you reload main table and use this script below should give you what you're after

PurChar(FieldName,'#')      AS NewField

[MarcoWedel]

Hi,

some similar solution to Marcus' for one table could be:

table1:
LOAD * FROM [https://community.qlik.com/servlet/JiveServlet/download/933463-201901/Data1.txt] (txt, codepage is 1252, embedded labels, delimiter is '|', msq);

tabTemp:
CrossTable (ColNam, ColVal)
LOAD 1,* Resident table1 Where RecNo()=1;

mapFieldRen:
Mapping LOAD ColNam, Trim(PurgeChar(ColNam,'@')) Resident tabTemp;

DROP Table tabTemp;

RENAME Fields using mapFieldRen;

hope this helps

regards

Marco

[hopkinsc]

hi Marco, i think yours and Marcus solutions would work but i have another problem.

once i have removed the @@ from the field names i then want to also use @@|@@ as a delimiter for the data.

when i try this is get errors, so what i want to try is to do as you said above, store back out then read back in with the @@|@@ delimiter then store out to QVD.

I would want to do this as part of the loop so it does all of my files

Does that make sense?

[MarcoWedel]

Hi,

maybe a solution to clean your field values as well could be something like:

tabRawData:
LOAD Replace([@1:n],'@@','') as RawData
FROM [https://community.qlik.com/servlet/JiveServlet/download/933463-201901/Data1.txt] (fix, utf8);

tabFinal:
LOAD * FROM_FIELD (tabRawData, RawData) (txt, utf8, no labels, delimiter is '|', msq);

tabHeaders:
CrossTable (NameOld, NameNew)
LOAD 1,* Resident tabFinal Where RecNo()=1;

mapFieldNames:
Mapping LOAD NameOld, NameNew Resident tabHeaders;

Right Join (tabFinal)
LOAD Distinct * Resident tabFinal Where RecNo()>1;

RENAME Fields using mapFieldNames;

DROP Tables tabRawData, tabHeaders;

hope this helps

regards

Marco

[MarcoWedel]

or something like:

tabTemp:
LOAD RecNo() as ID,
    IterNo() as SeqNo,
    SubField(RawData,'@@|@@',IterNo()) as SubRawData
While IterNo()<=SubStringCount(RawData,'@@|@@')+1;
LOAD Mid([@1:n],3,Len([@1:n])-4) as RawData
FROM [https://community.qlik.com/servlet/JiveServlet/download/933463-201901/Data1.txt] (fix, utf8);

mapFieldNames:
Mapping LOAD SeqNo, SubRawData Resident tabTemp
Where ID=1;

tabTemp2:
Generic
LOAD ID,
    SeqNo,
    SubRawData
Resident tabTemp
Where ID>1;

tabFinal:
LOAD Distinct ID
Resident tabTemp
Where ID>1;

DROP Table tabTemp;

FOR i = NoOfTables()-1 to 0 STEP -1
  LET vTable=TableName($(i));
  IF WildMatch('$(vTable)', 'tabTemp2.*') THEN
  LEFT JOIN (tabFinal) LOAD * RESIDENT [$(vTable)];
  DROP TABLE [$(vTable)];
  ENDIF
NEXT i

RENAME Fields using mapFieldNames;

DROP Field ID;

hope this helps

regards

Marco