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

[newqlikie]

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

[sebastiandperei]

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,

[newqlikie]

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

[newqlikie]

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

[Anonymous]

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

[sebastiandperei]

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;