mapping table within loop

[hopkinsc]

Hi all,

i have a loop in my script that uses a mapping table. the mapping table is used to rename field names.

If i only run it on one file then the output is as expected, but if it runs over multiple files i am getting field names in QVDs that are from the first file (if that makes sense)

here is my script

For Each vFile in FileList('*.txt')

Let vBasename = SubField(vFile, '.', 2);

TMP:

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 [$(vFile)]

(fix, utf8);

mapFieldNames:

mapping LOAD SeqNo, SubRawData Resident TMP

Where ID=1;

tabTemp2:

Generic

LOAD ID,

     SeqNo,

     SubRawData

Resident TMP

Where ID>1;

$(vBasename):

LOAD Distinct ID

Resident TMP

Where ID>1;

DROP Table TMP;

FOR i = NoOfTables()-1 to 0 STEP -1

  LET vTable=TableName($(i));

  IF WildMatch('$(vTable)', 'tabTemp2.*') THEN

  LEFT JOIN ($(vBasename)) LOAD * RESIDENT [$(vTable)];

  DROP TABLE [$(vTable)];

  ENDIF

NEXT i

RENAME Fields using mapFieldNames;

DROP Field ID;

Store $(vBasename) into $(vBasename).qvd(qvd);

Drop table $(vBasename);

next

does anyone know what i can do to 'clear' the mapping table before processing the next file in the loop?

[sunny_talwar]

Try this:

LET vInc = 1;

FOR Each vFile in FileList('*.txt')

LET vBasename = SubField(vFile, '.', 2);

TMP:

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 [$(vFile)]

(fix, utf8);

mapFieldNames$(vInc):

Mapping

LOAD SeqNo, SubRawData Resident TMP

Where ID=1;

tabTemp2:

Generic

LOAD ID,

    SeqNo,

    SubRawData

Resident TMP

Where ID>1;

$(vBasename):

LOAD Distinct ID

Resident TMP

Where ID>1;

DROP Table TMP;

FOR i = NoOfTables()-1 to 0 STEP -1

  LET vTable=TableName($(i));

  IF WildMatch('$(vTable)', 'tabTemp2.*') THEN

  LEFT JOIN ($(vBasename)) LOAD * RESIDENT [$(vTable)];

  DROP TABLE [$(vTable)];

  ENDIF

NEXT i

RENAME Fields using mapFieldNames;

DROP Field ID;

STORE $(vBasename) into $(vBasename).qvd(qvd);

DROP Table $(vBasename);

Let vInc = $(vInc) + 1;

NEXT

[Gabriel]

Hi,

This is one of undocumented because the Mapping table is not dropped until after the script finished then the Mapping table stays same during loop. What you have to do is to make the Mapping table unique for each loop. Try this

mapFieldNames$(vFile ):

mapping LOAD SeqNo, SubRawData Resident TMP

Where ID=1;

[hopkinsc]

Hi, thanks, i have tried this but it doesnt work either. that gives a mapping table not found error when i try and rename the fields using it.

[hopkinsc · Accepted solution]

Hi, thanks, this isnt working for me either.

here is my new script..

LET vMap = 1;

For Each vFile in FileList('*.txt')

Let vBasename = SubField(vFile, '.', 2);

TMP:

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 [$(vFile)]

(fix, utf8);

mapFieldNames$(vMap):

mapping LOAD SeqNo, SubRawData Resident TMP

Where ID=1;

tabTemp2:

Generic

LOAD ID,

     SeqNo,

     SubRawData

Resident TMP

Where ID>1;

$(vBasename):

LOAD Distinct ID

Resident TMP

Where ID>1;

DROP Table TMP;

FOR i = NoOfTables()-1 to 0 STEP -1

  LET vTable=TableName($(i));

  IF WildMatch('$(vTable)', 'tabTemp2.*') THEN

  LEFT JOIN ($(vBasename)) LOAD * RESIDENT [$(vTable)];

  DROP TABLE [$(vTable)];

  ENDIF

NEXT i

RENAME Fields using mapFieldNames$(vMap);

DROP Field ID;

Store $(vBasename) into $(vBasename).qvd(qvd);

Drop table $(vBasename);

Let vMap = $(vMap) + 1;

next

[Gabriel]

Did you change the ApplyMay to match 'mapFieldNames$(vFile )'?

[sunny_talwar]

I don't think you can use RENAME using mapping load. You need to use ApplyMap within a load statement to do this

RENAME Fields using mapFieldNames$(vMap);

This won't work

[hopkinsc]

Hi, i am not using an applymap. i am using RENAME Fields using mapFieldNames$(vFile);

[henrikalmen]

Try adding DROP TABLE tabTemp2; before your loop restarts.