Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld online is next week! REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
dsabzika
Contributor
Contributor

Subtract 2 QVD file

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);

2 Replies
tamilarasu
Champion
Champion

Hi Darren,

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

Capture.PNG

Try this,

FinalMTD:

LOAD *,

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

Resident WK1;

vishsaggi
Champion III
Champion III

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;