topic Re: Expression based on Dimension Help? in QlikView

Expression based on Dimension Help?

amit_saini — Mon, 15 Dec 2014 06:07:49 GMT

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,

Re: Expression based on Dimension Help?

amit_saini — Mon, 15 Dec 2014 07:04:09 GMT

Any suggestions here???

Thanks,

Re: Expression based on Dimension Help?

lironbaram — Mon, 15 Dec 2014 07:13:23 GMT

what aggregation formula, you are using for calculating the quantity

Re: Expression based on Dimension Help?

anbu1984 — Mon, 15 Dec 2014 07:13:55 GMT

Can you post sample app

Re: Expression based on Dimension Help?

Mon, 15 Dec 2014 07:25:01 GMT

Can you post your expression for Quantity.

Re: Expression based on Dimension Help?

amit_saini — Mon, 15 Dec 2014 09:06:18 GMT

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

Re: Expression based on Dimension Help?

amit_saini — Mon, 15 Dec 2014 09:10:41 GMT

Here is the attachment.

Thanks,

Re: Expression based on Dimension Help?

amit_saini — Mon, 15 Dec 2014 10:00:05 GMT

Hi Anbu,

Any suggestion here???

Thanks,
AS

Re: Expression based on Dimension Help?

anbu1984 — Mon, 15 Dec 2014 10:14:16 GMT

Did you group by only Level1 to calculate Quantity?

Can you post script?

Re: Expression based on Dimension Help?

amit_saini — Mon, 15 Dec 2014 10:18:25 GMT

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,

Re: Expression based on Dimension Help?

anbu1984 — Mon, 15 Dec 2014 13:27:31 GMT

Using your quantity expression in UI gives same value. Is this really duplicate? Did you verify by calculating quantity manually?

Check whether data is properly loaded in the table UnitCost