Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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,
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
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
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..
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;