Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

newqlikie
Contributor

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

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

Tags (2)
1 Solution

Accepted Solutions
s_uhlig
Contributor

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

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;

10 Replies
Not applicable

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

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

Not applicable

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

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

MVP & Luminary
MVP & Luminary

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

Hi all,

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

- Ralf

newqlikie
Contributor

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

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

MVP & Luminary
MVP & Luminary

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

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
Contributor

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

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;

Not applicable

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

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
Contributor

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

Hi Roland,

can you confirm the attached testcase produces the following result ?

Not applicable

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

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