Subtract 2 QVD file

[Anonymous]

Hi,

i have two qvd file that i'm trying to keep everything in PRJ_FACT_SALES_COGS_BAL_WK_A_2018_6. left join to PRJ_FACT_SALES_COGS_BAL_WK_A_2018_7_31.QVD and then subtract the column In CCC and then create another QVD file with difference. please can someone help. or shed some lights?

the code i have is follows but it is not working:

WK1:

LOAD GL_ACCOUNT_CODE,LEGAL_ENTITY_CODE,COMPANY_CODE,PROFIT_CENTER_CODE,MRC,VERSION_CODE,

//FISCAL_YEAR,FISCAL_YEAR_PERIOD_NUMBER,Week,


NTS_Factor,
[In CCC],TRANSACTIONAL_MTD_QTY,BRAVO_PRODUCT_CODE,BRAVO_PRODUCT_CODE as ORIG_BRAVO_PRODUCT_CODE,SourceType,
// VERSION_TYPE_CODE
%GEO_HIER_KEY,
%BRAVO_HIER_KEY,SRC_SYS_ID, [In CCC ORG]
FROM 

(qvd);

LEFT JOIN(WK1)

MTD:

LOAD GL_ACCOUNT_CODE,LEGAL_ENTITY_CODE,COMPANY_CODE,PROFIT_CENTER_CODE,MRC,VERSION_CODE,
//FISCAL_YEAR,FISCAL_YEAR_PERIOD_NUMBER,Week,
NTS_Factor,
[In CCC],TRANSACTIONAL_MTD_QTY,BRAVO_PRODUCT_CODE,BRAVO_PRODUCT_CODE as ORIG_BRAVO_PRODUCT_CODE,SourceType,
//VERSION_TYPE_CODE
%GEO_HIER_KEY,
%BRAVO_HIER_KEY,SRC_SYS_ID, [In CCC ORG]AS MTDValue
FROM

(qvd);

NoConcatenate
FinalMTD:
LOAD *, MTDValue-[In CCC] AS MTDDiff
Resident WK1;

Drop Table WK1;
Store FinalMTD INTO [$(vQVDPath)\WK2.qvd](qvd);

[tamilarasu]

Hi Darren,

You have null values in "MTDValue" column. That is why it is not working.

Try this,

FinalMTD:

LOAD *,

Alt(MTDValue,0) - Alt([In CCC],0) AS MTDDiff

Resident WK1;

[vishsaggi]

Try this?

WK1:

LOAD MRC,

     GL_ACCOUNT_CODE,

     LEGAL_ENTITY_CODE,

     COMPANY_CODE,

     PROFIT_CENTER_CODE,

     VERSION_CODE,

     FISCAL_YEAR,

     FISCAL_YEAR_PERIOD_NUMBER,

     Week,

     NTS_Factor,

     [In CCC],

     TRANSACTIONAL_MTD_QTY,

     BRAVO_PRODUCT_CODE,

     ORIG_BRAVO_PRODUCT_CODE,

     SourceType,

     VERSION_TYPE_CODE,

     %GEO_HIER_KEY,

     %BRAVO_HIER_KEY,

     SRC_SYS_ID,

     [In CCC ORG]

FROM

[C:\Users\dsabzika\Desktop\QVW Files\QVD\PRJ_FACT_SALES_COGS_BAL_WK_A_2018_6.QVD]

(qvd);

MTD:

LOAD MRC,

     GL_ACCOUNT_CODE,

     LEGAL_ENTITY_CODE,

     COMPANY_CODE,

     PROFIT_CENTER_CODE,

     VERSION_CODE,

     FISCAL_YEAR,

     FISCAL_YEAR_PERIOD_NUMBER,

     Week,

     NTS_Factor,

     [In CCC],

     TRANSACTIONAL_MTD_QTY,

     BRAVO_PRODUCT_CODE,

     ORIG_BRAVO_PRODUCT_CODE,

     SourceType,

     VERSION_TYPE_CODE,

     %GEO_HIER_KEY,

     %BRAVO_HIER_KEY,

     SRC_SYS_ID,

     [In CCC ORG]

FROM

[C:\Users\dsabzika\Desktop\QVW Files\QVD\PRJ_FACT_SALES_COGS_BAL_WK_A_2018_7_31.QVD]

(qvd);

NoConcatenate

FinalMTD:

LOAD *, [In CCC ORG] - [In CCC] AS MTDDiff

Resident WK1;

Drop Table WK1;