Erratic mapping behaviour

[#1]

I'm extracting information from a text file inseveral subdirectories using the recursive script somebody posted here recently, and the month names come in text (january, february, etc.). I'm using a mapping table to get the month numbers, but sometimes the mapping works (returns numbers correctly for all rows) and sometimes it only returns the number for some months, or sometimes none at all.

Any pointers as to what could be going on?

[biester]

Is the behaviour reproducible, meaning: does the same text file you're reading in always produce the same result, or does "sometimes" mean: with the same text file you sometimes get a correct result and sometimes not? Which version of QV are you using? 9.0 ?

Rgds,
Joachim

[#3]

biester wrote:
Is the behaviour reproducible, meaning: does the same text file you're reading in always produce the same result, or does "sometimes" mean: with the same text file you sometimes get a correct result and sometimes not? Which version of QV are you using? 9.0 ?
Rgds,
Joachim<div></div>

with exactly the same text file, processed with the same script, using the same inline mapping table sometimes I get a correct result and sometimes not. I'm using version: 8.50.6261.5

[biester]

Welllllll, as stable as 8.5 seemed to be the last days, but this sounds like a real big QlikView bug, if you can be sure that the text file has not changed (I'm thinking of a scenario where a text file with name XYZ.txt ist once saved as ASCII and the next day overwritten in a unicode format or a similar scenario). Sorry, but I ain't got any idea then. Perhaps you should harangue support with this if noone else here has a suggestion.

Rgds,
Joachim

[hector]

I got the same problem, but with QVD's files (instead your text files), sometimes works, sometimes doensn't.

I don't know why this happens, but i've resolved using lookup instead applymap.

regards.

[biester]

Hector,

thanks for the workaround, but anyway I'd suggest to report the thing to support as the problem seems to be common, and why not report a bug?

Rgds,
Joachim

[wizardo]

hi there

this comes from the refernce guide pdf:

Note: Mapping load does not support the use of QVD files as input source. The
QVD must first be loaded to a table and then a mapping load can be made
from this table.

so that answers the QVD problem, might be the same with txt

what you must do is load the table from qvd in a regular way and then use mapping load with resident from the preloadd table. i belive it will make the problems go away

Mansyno

[Oleg_Troyansky]

This behavior sounds very strange... I'm using mapping a lot, and I haven't seen anything like this happening, on any of QlikView versions.

I'd recommend checking out the formatting of the text that's being mapped. Since the text is coming from several different txt files, could there be some differences:

- Different case (lower/upper/mixed) ? would it help if you always converted the string into upper case, prior to mapping?

- Could there be any leading or training "spaces" that make the string look different than the one in the map? Would the problem go away if you used trim(text) prior to mapping?

If you don't provide a default value, the unmapped values with remain unmapped - can you see any pattern? Is it any specific month?

cheers,

[#9]

I have experienced the same issue and it is due to optimized versus normal load. The work around solution is to ensure that whenever you perform a mapping load from a QVD file to make sure that the load is not performed in an optimized way.

Check your data that needs to be mapped and just add a function to the load you are performing. So instead of :

map_month

mapping load distinct

     date                    as map_date,

     month                    as map_month

from table.qvd (qvd);

you should use

map_month

mapping load distinct

     date                    as map_date,

    text(month)         as map_month

from table.qvd (qvd);

If you do not want the data to be changed (if you think the text function might give an unpredictable result), just add a where clause to enforce a break in the optimzed load.

You will loose the optimized (faster) load, but I guess that for mapping tables the loss in performance is acceptable. Anyway, your applymap will not work otherwise. You can also do an optimized load and a mapping load from the resident table as already suggested.

In fact, the same rule applies in section access where a user table is loaded for example.