topic Re: remove ## from field names in QlikView

remove ## from field names

hopkinsc — Mon, 14 Dec 2015 13:54:08 GMT

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

Re: remove ## from field names

sunny_talwar — Mon, 14 Dec 2015 13:56:16 GMT

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

Re: remove ## from field names

Anonymous — Mon, 14 Dec 2015 13:57:26 GMT

check this as well:

How to rename a field in qlikview

How to Rename Fields

convert column name to upper case

Re: remove ## from field names

Peter_Cammaert — Mon, 14 Dec 2015 14:05:03 GMT

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(), '##', '') & ']';

TempTable:

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

STORE TempTable into [TextPath] (txt);

DROP Table Temp Table;

Best,

Peter

Re: remove ## from field names

pokassov — Mon, 14 Dec 2015 14:13:17 GMT

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

Re: remove ## from field names

hopkinsc — Mon, 14 Dec 2015 14:31:53 GMT

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?

Re: remove ## from field names

marcus_malinow — Mon, 14 Dec 2015 14:43:54 GMT

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

Re: remove ## from field names

tamilarasu — Mon, 14 Dec 2015 15:04:37 GMT

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

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

Re: remove ## from field names

hopkinsc — Mon, 14 Dec 2015 15:13:23 GMT

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

Re: remove ## from field names

sunny_talwar — Mon, 14 Dec 2015 21:16:49 GMT

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.

Re: remove ## from field names

Gabriel — Mon, 14 Dec 2015 22:22:48 GMT

Hi,

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

PurChar(FieldName,'#') AS NewField

Re: remove ## from field names

MarcoWedel — Tue, 15 Dec 2015 00:48:33 GMT

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

Re: remove ## from field names

hopkinsc — Tue, 15 Dec 2015 15:40:42 GMT

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?

Re: remove ## from field names

MarcoWedel — Tue, 15 Dec 2015 22:58:37 GMT

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

Re: remove ## from field names

MarcoWedel — Thu, 17 Dec 2015 23:45:24 GMT

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