Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Folks ,
I'm having one Material Number which goes through some levels say Level1 , Level 2 , Level3 ...etc.
For example in below pivot table Material Number 1024936001 is having links in other level. Here based on these levels I'm calling Quality as expression, but looks like something is wrong here. I'm getting duplicates entries like 1700 ,1700 etc.
But if I'm calling only level 1 than getting perfect values , see below:
Here what I want is to show all Levels and Description as above in first image, but Quality numbers should be based on Level1(Dimension).
Kindly help here.
Thanks,
AS
Any suggestions here???
Thanks,
AS
what aggregation formula, you are using for calculating the quantity
Can you post sample app
Can you post your expression for Quantity.
Hi,
Here it is , calling at the script side:
QuantityL1*if(not isnull(QuantityL2), QuantityL2, 1)*if(not isnull(QuantityL3), QuantityL3, 1)*if(not isnull(QuantityL4), QuantityL4, 1)*if(not isnull(QuantityL5), QuantityL5, 1) as Quality
Calling as Quality in Expression.
Thanks,
AS
Here is the attachment.
Thanks,
AS
Hi Anbu,
Any suggestion here???
Thanks,
AS
Did you group by only Level1 to calculate Quantity?
Can you post script?
Hi,
UnitCost_tmp:
load
*,
QuantityL1*if(not isnull(QuantityL2), QuantityL2, 1)*if(not isnull(QuantityL3), QuantityL3, 1)*if(not isnull(QuantityL4), QuantityL4, 1)*if(not isnull(QuantityL5), QuantityL5, 1) as Quantity,
subfield(CompUnitConcat, '|', -1) as ComponentUnit,
subfield(MatIDConcat, '|', -1) as MaterialID,
subfield(PlantConcat, '|', -1) as Plant,
subfield(MatIDConcat, '|', -1) & '|' & subfield(PlantConcat, '|', -1) as %MatPlantKey;
load
*,
ApplyMap('Map_IDtoDesc', MaterialIDL1, null()) as MaterialDesc0,
ApplyMap('Map_IDtoDesc', MaterialIDL2, null()) as MaterialDesc1,
ApplyMap('Map_IDtoDesc', MaterialIDL3, null()) as MaterialDesc2,
ApplyMap('Map_IDtoDesc', MaterialIDL4, null()) as MaterialDesc3,
ApplyMap('Map_IDtoDesc', MaterialIDL5, null()) as MaterialDesc4,
MaterialIDL1&if(not isnull(MaterialIDL2),'|'&MaterialIDL2)&if(not isnull(MaterialIDL3),'|'&MaterialIDL3)&if(not isnull(MaterialIDL4),'|'&MaterialIDL4)&if(not isnull(MaterialIDL5),'|'&MaterialIDL5) as MatIDConcat,
ComponentUnitL1&if(not isnull(ComponentUnitL2),'|'&ComponentUnitL2)&if(not isnull(ComponentUnitL3),'|'&ComponentUnitL3)&if(not isnull(ComponentUnitL4),'|'&ComponentUnitL4)&if(not isnull(ComponentUnitL5),'|'&ComponentUnitL5) as CompUnitConcat,
PlantL1&if(not isnull(PlantL2),'|'&PlantL2)&if(not isnull(PlantL3),'|'&PlantL3)&if(not isnull(PlantL4),'|'&PlantL4)&if(not isnull(PlantL5),'|'&PlantL5) as PlantConcat
resident UnitCost;
left join (UnitCost_tmp)
LOAD
replace(ltrim(replace(MATNR, '0', ' ')), ' ', 0) as MaterialID,
MTART as MaterialType,
MEINS as BaseUnit
FROM
$(vDataPath)\$(vSAP)\MARA.qvd
(qvd);
left join (UnitCost_tmp)
LOAD //MANDT,
WERKS as Plant,
WERKS & ' | '& NAME2 as PlantName
FROM
$(vDataPath)\$(vSAP)\T001W.qvd
(qvd);
left join (UnitCost_tmp)
LOAD
MTART as MaterialType,
//MTART & ' | '&
MTBEZ as MaterialTypeName
FROM
$(vDataPath)\$(vSAP)\T134T.qvd (qvd)
where SPRAS = 'E';
left join (UnitCost_tmp)
LOAD
replace(ltrim(replace(MATNR, '0', ' ')), ' ', 0) & '|' & BWKEY as %MatPlantKey,
BWKEY as ValuationArea,
STPRS as StandardPrice,
PEINH as PriceUnit
FROM
$(vDataPath)\$(vSAP)\MBEW.qvd
(qvd);
drop table UnitCost;
Also I have shared QVW in my previous comment.
Thanks,
AS