Problem with "Lookup" function in QV10. In QV9 is it perfectly working.

[newqlikie]

Hello,

Does anybody has the same problem? I have a script with a lookup function which is perfectly running with QlikView9 but when running the identical function in QlikView10, it is not working anymore. I attached the original files, one is loaded with QV9 and one in QV10 (both have the identical script).

Enclosed the script:

load*,    if((KRENAME1='OMNIDENT GMBH' or KRENAME1='OMNIDENT GmbH'),'Omnident GmbH',if(LiefNr<>169999,KRENAME1, '( keine Lieferantenzuordnung )')) as DU_Lieferant, //LiefNr 169999 wird ausgefiltert, da diese nur eine Puffernummer ist für die Artikelstammpflege, so lange keine richtige LiefNr vergeben wurde. Werte = 0
        if(LiefNr=165575,'Miro GmbH & Co. KG (nur Omnident)',                 // OmniLieferant hat keine DU-Artikel und somit keinen Bezug. Daher händische Namensvergabe
        if(LiefNr=165246,'Transcodent GmbH & Co. KG (nur Omnident)',         // OmniLieferant hat keine DU-Artikel und somit keinen Bezug. Daher händische Namensvergabe
        if(LiefNr=165626,'Werra Papier Wernshausen GmbH (nur Omnident)',     // OmniLieferant hat keine DU-Artikel und somit keinen Bezug. Daher händische Namensvergabe
        if(LiefNr>164999 and LiefNr<166000, lookup(KRENAME1,LiefNr,(LiefNr-5000))&' (nur Omnident)')))) as "OMNI_Lieferant"; //Analyse der OMNI Lieferanten
SQL SELECT //FIRMANUM,
    KREKONTO as "LiefNr",
    KRENAME1,
    KRENAME2 as "LiefName2",
    FIRMANUM as "Firmennummer",
    STRASSE as "LiefStraße",
    POSTLEIT as "LiefPLZ",
    ORTSBEZ as "LiefOrt",
    LAENDKEN as "LiefLand"
FROM NSFDAT.NSFSO
where FIRMANUM = '01';
drop field KRENAME1;

Explanation of the code:

The first suppliernames do not have a suppliernumber, which is e.g. 165575-5000 = 160575. So for those 3 suppliers, I added the name-transformation manually. for all others, i would like to use the "lookup"-function. Since QV10 it does not work anymore.

Do you have any idea why it is like this?

Even the QV-support has the problem since 6 months but I do not hear anything about it.

Thanks for your help.

Regards,

    NewQlikie

[#2]

Hello NewQlikie,

I was able to rebuild your results. Missing the original data-source I build a local szenario with exporting and reimporting your addresses. The reloading and matching with QV 9 SR6 works fine. But QV 10 SR2 doesn't.

So it seems to be a bug, I am going to report this to QT. Let's see what's happening.

Regards, Roland

[#3]

Hi NewQlikie,

now got a first reaction: It is a known bug in V10SR2. They can't say, when it will be solved, want to check it out with SR3.

So still hoping . . .

RR

[rbecher]

Hi all,

a pre build mapping table with LiefNr, KRENAME1 and applymap('maptable', LiefNr-5000) could help.

- Ralf

[newqlikie]

Thank you Ralf,

I hopefully can try it out in the afternoon.

I do my best, because I never worked with the "applymap"-function before.

- NewQlikie

[rbecher]

Something like that:

map_KRENAME1:

mapping load Distinct KRENAME1, LiefNr;

SQL SELECT Distinct KRENAME1, KREKONTO as "LiefNr"

FROM NSFDAT.NSFSO

where FIRMANUM = '01';

Replace your expression:

if(LiefNr>164999 and LiefNr<166000, applymap('map_KRENAME1',(LiefNr-5000))&' (nur Omnident)')))) as "OMNI_Lieferant"

- Ralf

[s_uhlig · Accepted solution]

Hello NewQlikie,

lookup seems to work in the form:

DATA:

LOAD * INLINE [

    F1, F2

    1, 4

    2, 3

    3, 2

    4, 1

];

left join(DATA)

load *, Lookup('F1','F2',F1,'DATA') as X resident DATA

;

may be you can give it a try.

Regards,

Sven

PS.:

NSFSO:

load*,    if((KRENAME1='OMNIDENT GMBH' or KRENAME1='OMNIDENT GmbH'),'Omnident GmbH',if(LiefNr<>169999,KRENAME1, '( keine Lieferantenzuordnung )')) as DU_Lieferant //LiefNr 169999 wird ausgefiltert, da diese nur eine Puffernummer ist für die Artikelstammpflege, so lange keine richtige LiefNr vergeben wurde. Werte = 0

;       

SQL SELECT //FIRMANUM,

    KREKONTO as "LiefNr",

    KRENAME1,

    KRENAME2 as "LiefName2",

    FIRMANUM as "Firmennummer",

    STRASSE as "LiefStraße",

    POSTLEIT as "LiefPLZ",

    ORTSBEZ as "LiefOrt",

    LAENDKEN as "LiefLand"

FROM NSFDAT.NSFSO

where FIRMANUM = '01';

left join(NSFSO) load LiefNr,

    if(LiefNr=165575,'Miro GmbH & Co. KG (nur Omnident)',                 // OmniLieferant hat keine DU-Artikel und somit keinen Bezug. Daher händische Namensvergabe

    if(LiefNr=165246,'Transcodent GmbH & Co. KG (nur Omnident)',         // OmniLieferant hat keine DU-Artikel und somit keinen Bezug. Daher händische Namensvergabe

    if(LiefNr=165626,'Werra Papier Wernshausen GmbH (nur Omnident)',     // OmniLieferant hat keine DU-Artikel und somit keinen Bezug. Daher händische Namensvergabe

    lookup('KRENAME1','LiefNr',(LiefNr-5000),'NSFSO')&' (nur Omnident)'))) as "OMNI_Lieferant" //Analyse der OMNI Lieferanten

resident NSFSO where LiefNr>164999 and LiefNr<166000;

drop field KRENAME1;

[#8]

Hello again,

--> Ralf:

No, I tested it always in same statement, table. I created an export and reimported the data. And I tried it with a field called RefLiefNr, containing LiefNr-5000. This doesn't work, too.

--> S. Uhlig.

Try it (more) similar to NewQlikies question above (Lookup('F1','F2' ,F1 -5000 ,'DATA').

The evaluating (and calc) of the expression is'nt done.

RR

[s_uhlig]

Hi Roland,

can you confirm the attached testcase produces the following result ?

[#10]

Hello again,

no, your script is loading same with my QV9SR6 as well as in QV10SR2.

May be a resident load is a workaround ?

RR