topic Re: Subtract 2 QVD file in QlikView

Subtract 2 QVD file

Anonymous — Thu, 09 Aug 2018 21:29:19 GMT

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

Re: Subtract 2 QVD file

tamilarasu — Fri, 10 Aug 2018 02:45:29 GMT

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;

Re: Subtract 2 QVD file

vishsaggi — Fri, 10 Aug 2018 03:09:41 GMT

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;