topic Load Statement to Multiply to values. in QlikView

Load Statement to Multiply to values.

bnelson111 — Wed, 25 Nov 2020 16:16:04 GMT

I have the following load statement which is working fine, however i wish to multiply the two values highlighted in red. Thanks in advance.

[WAsTE]:

LOAD CostCatNum,

CostType,

DeptCode,

CostcCode,

DimA,

StepNum,

JobCode as [Production Receipt Info.JobORder1],

ItemCode as WasteITEMCODE,

TxnDate,

ItemCode,

TxnQty;

SQL SELECT CostCatNum,

CostType,

DeptCode,

CostcCode,

DimA,

StepNum,

JobCode,

TxnDate,

ItemCode,

TxnQty

FROM PUB."PV_JobCosts"

WHERE ("TxnDate" > '01/01/2016') AND ("DeptCode" = '3-PRINT') AND ("CostType" = '1') AND ("CostcCode" = '3-PRINT') AND ("StepNum" < '1200') ;

UNQUALIFY *;

QUALIFY GSM;

left join

[WAsTE Item]:

LOAD GSM,

ItemCode as WasteITEMCODE;

SQL SELECT GSM,

ItemCode

FROM PUB."PM_Item"

WHERE GSM > '0';

UNQUALIFY *;

Re: Load Statement to Multiply to values.

YoussefBelloum — Tue, 28 Nov 2017 16:51:49 GMT

Hi,

are these two table linked ?

Re: Load Statement to Multiply to values.

MarcoWedel — Tue, 28 Nov 2017 20:42:02 GMT

Hi,

one script solution might be:

mapGSM:

Mapping

SQL SELECT ItemCode, GSM

FROM PUB."PM_Item"

WHERE GSM > '0';

[WAsTE]:

LOAD CostCatNum,

CostType,

DeptCode,

CostcCode,

DimA,

StepNum,

JobCode as [Production Receipt Info.JobORder1],

ItemCode as WasteITEMCODE,

TxnDate,

ItemCode,

TxnQty,

TxnQty*ApplyMap('mapGSM',ItemCode) as GSMTimesTxnQty;

SQL SELECT CostCatNum,

CostType,

DeptCode,

CostcCode,

DimA,

StepNum,

JobCode,

TxnDate,

ItemCode,

TxnQty

FROM PUB."PV_JobCosts"

WHERE ("TxnDate" > '01/01/2016') AND ("DeptCode" = '3-PRINT') AND ("CostType" = '1') AND ("CostcCode" = '3-PRINT') AND ("StepNum" < '1200') ;

hope this helps

regards

Marco

Re: Load Statement to Multiply to values.

bnelson111 — Wed, 29 Nov 2017 13:18:42 GMT

Brilliant thank you

Re: Load Statement to Multiply to values.

bnelson111 — Wed, 29 Nov 2017 13:18:50 GMT

Yep