Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Specialist
Specialist

Values not matching after the creation of transformed QVD

 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

Tags (2)
1 Reply
Highlighted
Specialist
Specialist

Re: Values not matching after the creation of transformed QVD

Hi All,

Could you please suggest on this.

Thanks