How to compare the records of two similar QVD's and list out the missing records!

[#1]

Hi All

May I know which is the easiest way to compare the two similar QVD's and list out the missing records? For example I have two similar QVD's one is coming from Production and other is coming from Development. There are few records which are missing in the QVD which are coming from the Development.So I want to know which is the easiest way to find those records which are missing in the QVD which is coming from the Development when compared to the QVD coming from production.

Can someone please help me out in their convenient time!

Thanks and Regards,
Attitude

[#2]

Hi PariPari and Wojciech

As discussed have attached the script for your reference. Hope you can help me out with this!

Development:
 
NoConcatenate
LOAD BASE_NO, 
     BRANCH, 
     CFRS_OU, 
     CFRS_OU_DESC, 
     COUNTRY, 
     EDOM, 
     EXP_COD, 
     EXP_COD_DESC, 
     FIRM_ACCT, 
     _FIRM_ACCT_NO, 
     FIRM_ACCT_NAM, 
     FIRM_COD, 
     FIRM_COD_DESC, 
     FRS_BU, 
     FRS_BU_DESC, 
     GLSL, 
     _GOC, 
     GOC_LVID, 
     GOC_STATUS, 
     HOLDCO_OU_FLAG, 
     LGL_ENTY, 
     LGL_VEH, 
     MCC, 
     MIS_RGN, 
     MIS_RGN_DESC, 
     ORG_UNIT, 
     OU_PREFIX, 
     OUC, 
     PAL_CAT, 
     PROC_DT, 
     PROD_CAT, 
     RDOM, 
     REPORTING_TYPE, 
     SRC_BRANCH, 
     SRC_PROC_DT, 
     SRC_RGN, 
     STGY_COD, 
     SUB_PROD, 
     SUB_STGY_COD, 
     TRACK_NO,
     'Not Available' AS MISSING
FROM
C:\Dev.qvd
(qvd);
 
//Production
Outer join (Development)
LOAD BASE_NO, 
     BRANCH, 
     CFRS_OU, 
     CFRS_OU_DESC, 
     COUNTRY, 
     EDOM, 
     EXP_COD, 
     EXP_COD_DESC, 
     FIRM_ACCT, 
     _FIRM_ACCT_NO, 
     FIRM_ACCT_NAM, 
     FIRM_COD, 
     FIRM_COD_DESC, 
     FRS_BU, 
     FRS_BU_DESC, 
     GLSL, 
     _GOC, 
     GOC_LVID, 
     GOC_STATUS, 
     HOLDCO_OU_FLAG, 
     LGL_ENTY, 
     LGL_VEH, 
     MCC, 
     MIS_RGN, 
     MIS_RGN_DESC, 
     ORG_UNIT, 
     OU_PREFIX, 
     OUC, 
     PAL_CAT, 
     PROC_DT, 
     PROD_CAT, 
     RDOM, 
     REPORTING_TYPE, 
     SRC_BRANCH, 
     SRC_PROC_DT, 
     SRC_RGN, 
     STGY_COD, 
     SUB_PROD, 
     SUB_STGY_COD, 
     TRACK_NO     
FROM
C:\Prd.qvd
(qvd);
    
Final:
LOAD BASE_NO, 
     BRANCH, 
     CFRS_OU, 
     CFRS_OU_DESC, 
     COUNTRY, 
     EDOM, 
     EXP_COD, 
     EXP_COD_DESC, 
     FIRM_ACCT, 
     _FIRM_ACCT_NO, 
     FIRM_ACCT_NAM, 
     FIRM_COD, 
     FIRM_COD_DESC, 
     FRS_BU, 
     FRS_BU_DESC, 
     GLSL, 
     _GOC, 
     GOC_LVID, 
     GOC_STATUS, 
     HOLDCO_OU_FLAG, 
     LGL_ENTY, 
     LGL_VEH, 
     MCC, 
     MIS_RGN, 
     MIS_RGN_DESC, 
     ORG_UNIT, 
     OU_PREFIX, 
     OUC, 
     PAL_CAT, 
     PROC_DT, 
     PROD_CAT, 
     RDOM, 
     REPORTING_TYPE, 
     SRC_BRANCH, 
     SRC_PROC_DT, 
     SRC_RGN, 
     STGY_COD, 
     SUB_PROD, 
     SUB_STGY_COD, 
     TRACK_NO
RESIDENT Development
Where IsNull(MISSING)=-1
;
Drop Table Development;

[#3]

Hi PariPari

Could you please help me out on this as it is very urgent!

Thanks,

Attitude

[#4]

paste First 1000 before each load

NoConcatenate
first 1000 LOAD BASE_NO,
     BRANCH,
     CFRS_OU,
     CFRS_OU_DESC,
     COUNTRY,
     EDOM,
     EXP_COD,
     EXP_COD_DESC,
     FIRM_ACCT,
     _FIRM_ACCT_NO,
     FIRM_ACCT_NAM,
     FIRM_COD,
     FIRM_COD_DESC,
     FRS_BU,
     FRS_BU_DESC,
     GLSL,
     _GOC,
     GOC_LVID,
     GOC_STATUS,
     HOLDCO_OU_FLAG,
     LGL_ENTY,
     LGL_VEH,
     MCC,
     MIS_RGN,
     MIS_RGN_DESC,
     ORG_UNIT,
     OU_PREFIX,
     OUC,
     PAL_CAT,
     PROC_DT,
     PROD_CAT,
     RDOM,
     REPORTING_TYPE,
     SRC_BRANCH,
     SRC_PROC_DT,
     SRC_RGN,
     STGY_COD,
     SUB_PROD,
     SUB_STGY_COD,
     TRACK_NO,
     'Not Available' AS MISSING
FROM
C:\Dev.qvd
(qvd);

//Production
Outer join (Development)
first 1000 LOAD BASE_NO,
     BRANCH,
     CFRS_OU,
     CFRS_OU_DESC,
     COUNTRY,
     EDOM,
     EXP_COD,
     EXP_COD_DESC,
     FIRM_ACCT,
     _FIRM_ACCT_NO,
     FIRM_ACCT_NAM,
     FIRM_COD,
     FIRM_COD_DESC,
     FRS_BU,
     FRS_BU_DESC,
     GLSL,
     _GOC,
     GOC_LVID,
     GOC_STATUS,
     HOLDCO_OU_FLAG,
     LGL_ENTY,
     LGL_VEH,
     MCC,
     MIS_RGN,
     MIS_RGN_DESC,
     ORG_UNIT,
     OU_PREFIX,
     OUC,
     PAL_CAT,
     PROC_DT,
     PROD_CAT,
     RDOM,
     REPORTING_TYPE,
     SRC_BRANCH,
     SRC_PROC_DT,
     SRC_RGN,
     STGY_COD,
     SUB_PROD,
     SUB_STGY_COD,
     TRACK_NO    
FROM
C:\Prd.qvd
(qvd);

and send a qvw file to me

[#5]

Hi PariPari

Sorry I cannot attach it as it is confidential. Thats the problem! 😞 Sorry again...

Thanks,

Attitude

[#6]

is there any kay field in this table?

and the second question....

do we realy have to do it in script?

[#7]

Imagine is there is no key field in this table then what could be done? May I know if it is not in script then how it could be done?

[#8]

in the presentation level:

(how big is your data set????)

[#9]

It is somewhere around 1,500,000 records(Approx)!

[#10]

I tried with the qvw which you attached with my data but it is still not giving the required result. It is showing all the records in the table. 🙂

[#11]

//development table + one marker column

test:

LOAD * INLINE [

    a, b, c

    1, 1, 1

];

//production

concatenate (test) LOAD * INLINE [

    a, b, c, mark

    1, 1, 1, x

    2, 2, 2, x

    3, 3, 3, x

];

final:

noconcatenate load a,b,c,mark resident test where isnull(mark)<>-1;

drop table test;