Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
Hi PariPari
Could you please help me out on this as it is very urgent!
Thanks,
Attitude
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
Hi PariPari
Sorry I cannot attach it as it is confidential. Thats the problem! 😞 Sorry again...
Thanks,
Attitude
is there any kay field in this table?
and the second question....
do we realy have to do it in script?
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?
in the presentation level:
(how big is your data set????)
It is somewhere around 1,500,000 records(Approx)!
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. 🙂
//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;