Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Highlighted
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
Highlighted
Honored Contributor III

Re: Expression based on Dimension Help?

Any suggestions here???

Thanks,

AS

Highlighted
Partner
Partner

Re: Expression based on Dimension Help?

what aggregation formula, you are using for calculating the quantity

Highlighted
Honored Contributor III

Re: Expression based on Dimension Help?

Can you post sample app

Highlighted
Not applicable

Re: Expression based on Dimension Help?

Can you post your expression for Quantity.

Highlighted
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

Highlighted
Honored Contributor III

Re: Expression based on Dimension Help?

Here is the attachment.

Thanks,

AS

Highlighted
Honored Contributor III

Re: Expression based on Dimension Help?

Hi Anbu,

Any suggestion here???

Thanks,
AS

Highlighted
Honored Contributor III

Re: Expression based on Dimension Help?

Did you group by only Level1 to calculate Quantity?

Can you post script?

Highlighted
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