10 Replies Latest reply: Dec 15, 2014 8:27 AM by anbu cheliyan RSS

    Expression based on Dimension Help?

    Amit Saini

      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

        • Re: Expression based on Dimension Help?
          Amit Saini

          Any suggestions here???

           

          Thanks,

          AS

          • Re: Expression based on Dimension Help?
            anbu cheliyan

            Can you post sample app

              • Re: Expression based on Dimension Help?
                Amit Saini

                Hi Anbu,

                 

                Any suggestion here???

                 

                 

                Thanks,
                AS

                  • Re: Expression based on Dimension Help?
                    anbu cheliyan

                    Did you group by only Level1 to calculate Quantity?

                     

                    Can you post script?

                      • Re: Expression based on Dimension Help?
                        Amit Saini

                        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

                  • Re: Expression based on Dimension Help?
                    sindhu kannan

                    Can you post your expression for Quantity.