Values not matching after the creation of transformed QVD

[deepakqlikview_123]

 Hi All,

I am using the below piece of code and after the creation of the transformed QVDs the values are not matching.

NewData:
LOAD *,
[Siebel Key] as [SAP_Siebel_Key],
if(applymap('TECO_Map',[Sales Document Item Key],null())='X',1,0) as [TECO flag],
applymap('BusinessLineMap',%CO_AREA_PROFIT_CTR,null()) as BUSINESSLINE_TMP,
applymap('BusinessUnitMap',%CO_AREA_PROFIT_CTR,null()) as BUSINESSUNIT_TMP,
applymap('BusinesAreaMap',%CO_AREA_PROFIT_CTR,null()) as BUSINESSAREA_TMP,
applymap('SoldToCustomerNameMap',[%Sold-to Party_SOLD_TO],null()) as SOLD_TO_CUSTOMER_TMP,
applymap('SoldToCustomerNameCodeMap',[%Sold-to Party_SOLD_TO],null()) as SOLD_TO_CUSTOMER_CODE_TMP,
applymap('PayerNameMap',%Payer_PAYER,null()) as PAYER_TMP,
ApplyMap('PayerCodeMap',%Payer_PAYER,null()) as PAYER_CODE_TMP,
applymap('SAPCompanyRegionCodeMAP',[%Company code_COMP_CODE],null()) as COMPANY_REGION_CODE_TMP,
ApplyMap('SPCodeMap',[%MaterialGroup 1_MATL_GRP_1],'N/A') as SP_TMP,
ApplyMap('SalesOfficeMap',[%Sales Office_SALES_OFF],null()) as SALES_OFFICE_TMP,
ApplyMap('SAP Company MAP',[%Company code_COMP_CODE],null()) as COMPANY_TMP,
//ApplyMap('SAPRejectedOrdersMAP',[%Reason for rejection_REASON_REJ],'No') as REJECTED_ORDER_TMP, // commented by BI-3705 - PN
///text(date(date#(applymap('SalesDocumentDateMap',[Date Key]),'YYYY-MM-DD'),'YYYYMM')) as YEARMONTH_TMP,
///year(date#(applymap('SalesDocumentDateMap',[Date Key]),'YYYY-MM-DD')) as YEAR_TMP,
///month(date#(applymap('SalesDocumentDateMap',[Date Key]),'YYYY-MM-DD')) as MONTH_TMP,
///date(date#(applymap('SalesDocumentDateMap',[Date Key]),'YYYY-MM-DD'),'YYYYMM') &'-'&if(isnull([Siebel Key]) or Match([Siebel Key],'AGR- ','N/A'), [Sales Document Item Key],[Siebel Key]) as [%Common Global Key]
//if(isnull([Siebel Key]) or Match([Siebel Key],'AGR- ','N/A'), [Sales Document Item Key],[Siebel Key]) as [%Common Global Key] //removed
[Sales Document Item Key] as [%Common Global Key],
//if(Wildmatch([Siebel Key],'AGR-*') and len(trim([Siebel Key]))>4,null(),if([Internal Reference]<>' ',[Internal Reference],[Sales Document Number])) as [FS Sales Order], //if correct agreement then show empty otherwise Internal Reference/Sales Document number
if(Wildmatch([Siebel Key],'AGR-*') and len(trim([Siebel Key]))>4,null(),if(Wildmatch([Siebel Key],'SR-*') and len(trim([Siebel Key]))>3,if([Internal Reference]<>' ',[Internal Reference] , [Sales Document Number]), [Sales Document Number])) as [FS Sales Order], //if correct agreement then null else if correct SR then (if exists ref. number then ref. number else sales document) else sales document
UPPER([%Company code_COMP_CODE]) as [%COMPANY_CODE_OR_AREA] //link for Section Access
RESIDENT Data
Where QVDsource='SalesOrder' AND Len( Purgechar( [%Reason for rejection_REASON_REJ], Chr(09) & Chr(32) & Chr(160) & Chr(12288)) ) = 0
/*len(Trim([%Reason for rejection_REASON_REJ]))=0*/; // commented by BI-3705 - PN

LOAD *,
[Siebel Key] as [SAP_Siebel_Key],
if(applymap('TECO_Map',[Sales Document Item Key],null())='X',1,0) as [TECO flag],
applymap('BusinessLineMap',%CO_AREA_PROFIT_CTR,null()) as BUSINESSLINE_TMP,
applymap('BusinessUnitMap',%CO_AREA_PROFIT_CTR,null()) as BUSINESSUNIT_TMP,
applymap('BusinesAreaMap',%CO_AREA_PROFIT_CTR,null()) as BUSINESSAREA_TMP,
applymap('SoldToCustomerNameMap',[%Sold-to Party_SOLD_TO],null()) as SOLD_TO_CUSTOMER_TMP,
applymap('SoldToCustomerNameCodeMap',[%Sold-to Party_SOLD_TO],null()) as SOLD_TO_CUSTOMER_CODE_TMP,
applymap('PayerNameMap',%Payer_PAYER,null()) as PAYER_TMP,
ApplyMap('PayerCodeMap',%Payer_PAYER,null()) as PAYER_CODE_TMP,
applymap('SAPCompanyRegionCodeMAP',[%Company code_COMP_CODE],null()) as COMPANY_REGION_CODE_TMP,
ApplyMap('SPCodeMap',[%MaterialGroup 1_MATL_GRP_1],'N/A') as SP_TMP,
ApplyMap('SalesOfficeMap',[%Sales Office_SALES_OFF],null()) as SALES_OFFICE_TMP,
ApplyMap('SAP Company MAP',[%Company code_COMP_CODE],null()) as COMPANY_TMP,
//ApplyMap('SAPRejectedOrdersMAP',[%Reason for rejection_REASON_REJ],'No') as REJECTED_ORDER_TMP, // commented by BI-3705 - PN
///text(date(date#(applymap('SalesDocumentDateMap',[Date Key]),'YYYY-MM-DD'),'YYYYMM')) as YEARMONTH_TMP,
///year(date#(applymap('SalesDocumentDateMap',[Date Key]),'YYYY-MM-DD')) as YEAR_TMP,
///month(date#(applymap('SalesDocumentDateMap',[Date Key]),'YYYY-MM-DD')) as MONTH_TMP,
///date(date#(applymap('SalesDocumentDateMap',[Date Key]),'YYYY-MM-DD'),'YYYYMM') &'-'&if(isnull([Siebel Key]) or Match([Siebel Key],'AGR- ','N/A'), [Sales Document Item Key],[Siebel Key]) as [%Common Global Key]
//if(isnull([Siebel Key]) or Match([Siebel Key],'AGR- ','N/A'), [Sales Document Item Key],[Siebel Key]) as [%Common Global Key] //removed
[Sales Document Item Key] as [%Common Global Key],
if(Wildmatch([Siebel Key],'AGR-*') and len(trim([Siebel Key]))>4,null(),if(Wildmatch([Siebel Key],'SR-*') and len(trim([Siebel Key]))>3,if([Internal Reference]<>' ',[Internal Reference] , [Sales Document Number]), [Sales Document Number])) as [FS Sales Order], //if correct agreement then null else if correct SR then (if exists ref. number then ref. number else sales document) else sales document
UPPER([%Company code_COMP_CODE]) as [%COMPANY_CODE_OR_AREA] //link for Section Access
RESIDENT Data
Where QVDsource='Orderbook' and Len( Purgechar( [%Reason for rejection_REASON_REJ], Chr(09) & Chr(32) & Chr(160) & Chr(12288)) ) = 0
/*len(Trim([%Reason for rejection_REASON_REJ]))=0*/; // commented by BI-3705 - PN

LOAD *,
[Siebel Key] as [SAP_Siebel_Key],
if(applymap('TECO_Map',[Sales Document Item Key],null())='X',1,0) as [TECO flag],
applymap('BusinessLineMap',%CO_AREA_PROFIT_CTR,null()) as BUSINESSLINE_TMP,
applymap('BusinessUnitMap',%CO_AREA_PROFIT_CTR,null()) as BUSINESSUNIT_TMP,
applymap('BusinesAreaMap',%CO_AREA_PROFIT_CTR,null()) as BUSINESSAREA_TMP,
applymap('SoldToCustomerNameMap',[%Sold-to Party_SOLD_TO],null()) as SOLD_TO_CUSTOMER_TMP,
applymap('SoldToCustomerNameCodeMap',[%Sold-to Party_SOLD_TO],null()) as SOLD_TO_CUSTOMER_CODE_TMP,
applymap('PayerNameMap',%Payer_PAYER,null()) as PAYER_TMP,
ApplyMap('PayerCodeMap',%Payer_PAYER,null()) as PAYER_CODE_TMP,
applymap('SAPCompanyRegionCodeMAP',[%Company code_COMP_CODE],null()) as COMPANY_REGION_CODE_TMP,
ApplyMap('SPCodeMap',[%MaterialGroup 1_MATL_GRP_1],'N/A') as SP_TMP,
ApplyMap('SalesOfficeMap',[%Sales Office_SALES_OFF],null()) as SALES_OFFICE_TMP,
ApplyMap('SAP Company MAP',[%Company code_COMP_CODE],null()) as COMPANY_TMP,
//ApplyMap('SAPRejectedOrdersMAP',[%Reason for rejection_REASON_REJ],'No') as REJECTED_ORDER_TMP, // commented by BI-3705 - PN
///text(date(date#(applymap('IvoiceDocumentDateMap',[Date Key]),'YYYY-MM-DD'),'YYYYMM')) as YEARMONTH_TMP,
///year(date#(applymap('IvoiceDocumentDateMap',[Date Key]),'YYYY-MM-DD')) as YEAR_TMP,
///month(date#(applymap('IvoiceDocumentDateMap',[Date Key]),'YYYY-MM-DD')) as MONTH_TMP,
///date(date#(applymap('IvoiceDocumentDateMap',[Date Key]),'YYYY-MM-DD'),'YYYYMM') &'-'&if(isnull([Siebel Key]) or Match([Siebel Key],'AGR- ','N/A'), [Billing Document Item Key],[Siebel Key]) as [%Common Global Key]
//if(isnull([Siebel Key]) or Match([Siebel Key],'AGR- ','N/A'), [Billing Document Item Key],[Siebel Key]) as [%Common Global Key] //removed
[Billing Document Item Key] as [%Common Global Key],
if(Wildmatch([Siebel Key],'AGR-*') and len(trim([Siebel Key]))>4,null(),if(Wildmatch([Siebel Key],'SR-*') and len(trim([Siebel Key]))>3,if([Internal Reference]<>' ',[Internal Reference] , [Sales Document Number]), [Sales Document Number])) as [FS Sales Order], //if correct agreement then null else if correct SR then (if exists ref. number then ref. number else sales document) else sales document
UPPER([%Company code_COMP_CODE]) as [%COMPANY_CODE_OR_AREA] //link for Section Access
RESIDENT Data
Where QVDsource='Invoicing' AND Len( Purgechar( [%Reason for rejection_REASON_REJ], Chr(09) & Chr(32) & Chr(160) & Chr(12288)) ) = 0
/*len(Trim([%Reason for rejection_REASON_REJ]))=0*/; // commented by BI-3705 - PN

LOAD *,
[Siebel Key] as [SAP_Siebel_Key],
if(applymap('TECO_Map',[Sales Document Item Key],null())='X',1,0) as [TECO flag],
applymap('BusinessLineMap',%CO_AREA_PROFIT_CTR,null()) as BUSINESSLINE_TMP,
applymap('BusinessUnitMap',%CO_AREA_PROFIT_CTR,null()) as BUSINESSUNIT_TMP,
applymap('BusinesAreaMap',%CO_AREA_PROFIT_CTR,null()) as BUSINESSAREA_TMP,
applymap('SoldToCustomerNameMap',[%Sold-to Party_SOLD_TO],null()) as SOLD_TO_CUSTOMER_TMP,
applymap('SoldToCustomerNameCodeMap',[%Sold-to Party_SOLD_TO],null()) as SOLD_TO_CUSTOMER_CODE_TMP,
applymap('PayerNameMap',%Payer_PAYER,null()) as PAYER_TMP,
ApplyMap('PayerCodeMap',%Payer_PAYER,null()) as PAYER_CODE_TMP,
applymap('SAPCompanyRegionCodeMAP',[%Company code_COMP_CODE],null()) as COMPANY_REGION_CODE_TMP,
ApplyMap('SPCodeMap',[%MaterialGroup 1_MATL_GRP_1],'N/A') as SP_TMP,
ApplyMap('SalesOfficeMap',[%Sales Office_SALES_OFF],null()) as SALES_OFFICE_TMP,
ApplyMap('SAP Company MAP',[%Company code_COMP_CODE],null()) as COMPANY_TMP,
//ApplyMap('SAPRejectedOrdersMAP',[%Reason for rejection_REASON_REJ],'No') as REJECTED_ORDER_TMP,
///text(date(date#(applymap('IvoiceDocumentDateMap',[Date Key]),'YYYY-MM-DD'),'YYYYMM')) as YEARMONTH_TMP,
///year(date#(applymap('IvoiceDocumentDateMap',[Date Key]),'YYYY-MM-DD')) as YEAR_TMP,
///month(date#(applymap('IvoiceDocumentDateMap',[Date Key]),'YYYY-MM-DD')) as MONTH_TMP,
///date(date#(applymap('IvoiceDocumentDateMap',[Date Key]),'YYYY-MM-DD'),'YYYYMM') &'-'&if(isnull([Siebel Key]) or Match([Siebel Key],'AGR- ','N/A'), [Billing Document Item Key],[Siebel Key]) as [%Common Global Key]
//if(isnull([Siebel Key]) or Match([Siebel Key],'AGR- ','N/A'), [Billing Document Item Key],[Siebel Key]) as [%Common Global Key] //removed
[Billing Document Item Key] as [%Common Global Key],
if(Wildmatch([Siebel Key],'AGR-*') and len(trim([Siebel Key]))>4,null(),if(Wildmatch([Siebel Key],'SR-*') and len(trim([Siebel Key]))>3,if([Internal Reference]<>' ',[Internal Reference] , [Sales Document Number]), [Sales Document Number])) as [FS Sales Order], //if correct agreement then null else if correct SR then (if exists ref. number then ref. number else sales document) else sales document
UPPER([%Company code_COMP_CODE]) as [%COMPANY_CODE_OR_AREA] //link for Section Access
RESIDENT Data
Where QVDsource='Billing' AND Len( Purgechar( [%Reason for rejection_REASON_REJ], Chr(09) & Chr(32) & Chr(160) & Chr(12288)) ) = 0

DIRECTORY $(VOPTQVD);

store NewData into NewData.qvd(qvd);

drop table NewData;

Any help would be appreciated.

Thanks

[deepakqlikview_123]

Hi All,

Could you please suggest on this.

Thanks