Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
amit_saini
Master III
Master III

Expression based on Dimension Help?

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

10 Replies
amit_saini
Master III
Master III
Author

Any suggestions here???

Thanks,

AS

lironbaram
Partner - Master III
Partner - Master III

what aggregation formula, you are using for calculating the quantity

anbu1984
Master III
Master III

Can you post sample app

Not applicable

Can you post your expression for Quantity.

amit_saini
Master III
Master III
Author

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

amit_saini
Master III
Master III
Author

Here is the attachment.

Thanks,

AS

amit_saini
Master III
Master III
Author

Hi Anbu,

Any suggestion here???

Thanks,
AS

anbu1984
Master III
Master III

Did you group by only Level1 to calculate Quantity?

Can you post script?

amit_saini
Master III
Master III
Author

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