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

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

newqlikie — Fri, 01 Jul 2011 11:26:13 GMT

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

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

Fri, 01 Jul 2011 12:56:17 GMT

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

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

Fri, 01 Jul 2011 14:59:21 GMT

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 . . .

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

rbecher — Tue, 05 Jul 2011 09:05:15 GMT

Hi all,

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

- Ralf

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

newqlikie — Tue, 05 Jul 2011 09:34:32 GMT

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

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

rbecher — Tue, 05 Jul 2011 09:51:21 GMT

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

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

s_uhlig — Tue, 05 Jul 2011 09:54:41 GMT

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;

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

Tue, 05 Jul 2011 10:48:57 GMT

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.

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

s_uhlig — Tue, 05 Jul 2011 11:29:18 GMT

Hi Roland,

can you confirm the attached testcase produces the following result ?

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

Tue, 05 Jul 2011 12:17:03 GMT

Hello again,

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

May be a resident load is a workaround ?

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

newqlikie — Thu, 07 Jul 2011 12:00:20 GMT

Thank you very much to your all.

Also thank you to S.Uhlig. Your option (our remodelled part of the script) is perfectly running.

I am really happy about that solution, becaus it was not working for about 5 months.

Thanks again,

NewQlikie