topic Re: mapping table within loop in QlikView

mapping table within loop

hopkinsc — Fri, 18 Dec 2015 10:46:11 GMT

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

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

Re: mapping table within loop

sunny_talwar — Fri, 18 Dec 2015 11:08:31 GMT

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

Re: mapping table within loop

Gabriel — Fri, 18 Dec 2015 11:34:52 GMT

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;

Re: mapping table within loop

hopkinsc — Fri, 18 Dec 2015 11:57:16 GMT

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.

Re: mapping table within loop

hopkinsc — Fri, 18 Dec 2015 12:01:27 GMT

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;

Re: mapping table within loop

Gabriel — Fri, 18 Dec 2015 12:02:03 GMT

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

Re: mapping table within loop

sunny_talwar — Fri, 18 Dec 2015 12:04:35 GMT

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

Re: mapping table within loop

hopkinsc — Fri, 18 Dec 2015 12:32:40 GMT

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

Re: mapping table within loop

henrikalmen — Fri, 18 Dec 2015 13:42:03 GMT

Try adding DROP TABLE tabTemp2; before your loop restarts.