topic Re: Problem with Mapping-function in QlikView

Problem with Mapping-function - count a field to get one value

newqlikie — Thu, 23 Aug 2012 12:25:02 GMT

Hello,

I have the problem, that my field out of the "mapping" function will not be found.

I always get following error message (Field not found - <SUMABART>):

Here is the used script:

Temp:
load *, date(today()-1) as datum;
SQL SELECT SAARTNR,
    SALOEKEN,
    SAARTBEZ1,
    SAABCKZ
FROM QS36F.DLSSA
    WHERE SALOEKEN <> 'L' and SAABCKZ <> 'C' and SAABCKZ <> '' and SAABCKZ <> '' and SAURSPRU='M' ;
left join
SQL SELECT ARTIKELNUMMER as SAARTNR,
    RUECKSTAND
FROM V7R1.QS36F.DLSSBV01;
Neue_Tabelle:
MAPPING LOAD DISTINCT
    count(SAABCKZ) AS SUMABART,
        SAARTNR
RESIDENT Temp
    GROUP BY SAARTNR;
Artikel:
Load *,
    datum as a,
    SAARTNR as b,
    SALOEKEN as c,
    SAARTBEZ1 as d,
    SAABCKZ as e,
    ApplyMap('Neue_Tabelle',SUMABART) as SUMAB
RESIDENT Temp;
DROP TABLE Temp;

What do I do wrong?

Thanks for your help.

Regadrs

NewQlikie

Re: Problem with Mapping-function

sebastiandperei — Thu, 23 Aug 2012 12:35:13 GMT

Hi!

You need the field SUMABART in Temp table, to make the "implicit join" that the ApplyMap function does.

I think that if you use SAARTNR instead of SUMABART in ApplyMap, it will works properly,

Re: Problem with Mapping-function

newqlikie — Thu, 23 Aug 2012 12:42:43 GMT

Hi,

thnaks for your fast response, buit the problem is, that I need a new value.

Do I understand right. I load a field in the Temp table, which I do not need.

By using the Mapping-function, I overwrite the "not needed field" to get my calculation?

Thanks again for your fast help.

Regards,

NewQlikie

Re: Problem with Mapping-function

newqlikie — Thu, 23 Aug 2012 12:50:20 GMT

Hi,

sorry I forgot to explain the main problem.

I need to count the number of values in a table.

In above sample the number of Article-numbers with an A or B classification.

So I need to count the field "SAABCKZ" to get only "ONE" value which I use.

Hope it is clear.

I think, I do something totally wrong!

Regards,

NewQlikie

Re: Problem with Mapping-function

Anonymous — Thu, 23 Aug 2012 13:09:10 GMT

here.. try this out..

Temp:
load *, date(today()-1) as datum;
SQL SELECT SAARTNR,
    SALOEKEN,
    SAARTBEZ1,
    SAABCKZ
FROM QS36F.DLSSA
    WHERE SALOEKEN <> 'L' and SAABCKZ <> 'C' and SAABCKZ <> '' and SAABCKZ <> '' and SAURSPRU='M' ;
left join
SQL SELECT ARTIKELNUMMER as SAARTNR,
    RUECKSTAND
FROM V7R1.QS36F.DLSSBV01;
Neue_Tabelle:
MAPPING LOAD DISTINCT
        SAARTNR,
    count(SAABCKZ) AS SUMABART
RESIDENT Temp
    GROUP BY SAARTNR;
Artikel:
Load *,
    datum as a,
    SAARTNR as b,
    SALOEKEN as c,
    SAARTBEZ1 as d,
    SAABCKZ as e,
    ApplyMap('Neue_Tabelle',SAARTNR) as SUMAB
RESIDENT Temp;
DROP TABLE Temp;

modified parts are the mapping table load.. and the applymap statement..

hope that helps..

Re: Problem with Mapping-function

sebastiandperei — Thu, 23 Aug 2012 13:14:29 GMT

Ok, you need only one value of the number (Count) of SAABCKZ for each SAARTNR. Try with this (replace your script since Neue_Tabelle, so , comment this table)

Artikel: Load *, datum as a, SAARTNR as b, SALOEKEN as c, SAARTBEZ1 as d, SAABCKZ as e RESIDENT Temp;

Left Join

LOAD DISTINCT count(SAABCKZ) AS SUMABART, SAARTNR RESIDENT Temp GROUP BY SAARTNR;

DROP TABLE Temp;