Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
NoConcatenate
FinalMTD:
LOAD *, MTDValue-[In CCC] AS MTDDiff
Resident WK1;
Drop Table WK1;
Store FinalMTD INTO [$(vQVDPath)\WK2.qvd](qvd);
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;
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;