Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
amit_saini
Honored Contributor 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
Honored Contributor III

Re: Expression based on Dimension Help?

Any suggestions here???

Thanks,

AS

lironbaram
Honored Contributor II

Re: Expression based on Dimension Help?

what aggregation formula, you are using for calculating the quantity

anbu1984
Honored Contributor III

Re: Expression based on Dimension Help?

Can you post sample app

Not applicable

Re: Expression based on Dimension Help?

Can you post your expression for Quantity.

amit_saini
Honored Contributor III

Re: Expression based on Dimension Help?

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
Honored Contributor III

Re: Expression based on Dimension Help?

Here is the attachment.

Thanks,

AS

amit_saini
Honored Contributor III

Re: Expression based on Dimension Help?

Hi Anbu,

Any suggestion here???

Thanks,
AS

anbu1984
Honored Contributor III

Re: Expression based on Dimension Help?

Did you group by only Level1 to calculate Quantity?

Can you post script?

amit_saini
Honored Contributor III

Re: Expression based on Dimension Help?

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

Community Browser