Left Join Problems

tmumaw — Thu, 14 Jan 2010 14:56:43 GMT

Hello All:

I am have some problems doing my left join. I have 2 tables from SAP (VBAK (Sales Order Header), and VBAP(Sales Order Line Detail). I am trying to do a left join on the document number. It keeps giving me errors saying "VBAP_Temp table not found". Thanks in advance. Here is my code:

//**********************************************************************************
// Table: VBAK - Sales Order Document Header
//**********************************************************************************

[VBAK]: // Sales Document: Header Data
Load
[VBELN] as [%SalesOrderDocument_Key],
[VKORG] as [Sales Organization_VKORG],
[VTWEG] as [Distribution Channel_VTWEG],
[SPART] as [Division_SPART],
[VBTYP] as [SD document categ._VBTYP],
[AUART] as [Sales Document Type_AUART],
[KURST] as [Exchange Rate Type_KURST],
[VBELN] as [Sales Document_VBELN],
[KUNNR] as [Sold-to party_KUNNR],
[VKBUR] as [Sales Office_VKBUR],
[VKGRP] as [Sales Group_VKGRP],
[AUFNR] as [Order_AUFNR],
[AUGRU] as [Order reason_AUGRU],
[WAERK] as [Document Currency_WAERK],
[GUEBG] as [Valid-from date_GUEBG],
[GUEEN] as [Valid-to date_GUEEN],
[STWAE] as [Statistics currency_STWAE],
[KVGR1] as [Customer group 1_KVGR1],
[KVGR2] as [Customer group 2_KVGR2],
[KVGR3] as [Customer group 3_KVGR3],
[KVGR4] as [Customer group 4_KVGR4],
[KVGR5] as [Customer group 5_KVGR5];
// [KOSTL] as [Cost Center_KOSTL],
// [NETWR] as [Net value_NETWR],
// [VGBEL] as [Reference document_VGBEL],
// [ERNAM] as [Created by_ERNAM],
// [ERZET] as [Time_ERZET],
// [ERDAT] as [Created on_ERDAT],
// [AEDAT] as [Changed on_AEDAT],
// [KNUMA] as [Agreement_KNUMA],
// [BSTNK] as [Purchase order no._BSTNK],
// [BSTDK] as [Purchase order date_BSTDK],
// [STAFO] as [Update group (stats)_STAFO],
// [ANGDT] as [Quotation valid from_ANGDT],
// [BNDDT] as [Quotation valid to_BNDDT],
// [AUDAT] as [Document Date_AUDAT],
// [TRVOG] as [Transaction group_TRVOG],
// [GWLDT] as [Guarantee_GWLDT],
// [SUBMI] as [Collective number_SUBMI],
// [LIFSK] as [Delivery block_LIFSK],
// [FAKSK] as [Billing block_FAKSK],
// [GSBER] as [Business Area_GSBER],
// [GSKST] as [Business area_GSKST],
// [KNUMV] as [Doc. condition no._KNUMV],
// [VDATU] as [Requested deliv.date_VDATU],
// [VPRGR] as [Prop.date type_VPRGR],
// [AUTLF] as [Complete delivery_AUTLF],
// [VBKLA] as [Original system_VBKLA],
// [VBKLT] as [Indicator_VBKLT],
// [KALSM] as [Pricing procedure_KALSM],
// [VSBED] as [Shipping Conditions_VSBED],
// [FKARA] as [Order-rel.bill.type_FKARA],
// [AWAHR] as [Probability_AWAHR],
// [KTEXT] as [Description_KTEXT],
// [BSARK] as [Purchase order type_BSARK],
// [BSTZD] as [Supplement_BSTZD],
// [IHREZ] as [Your Reference_IHREZ],
// [BNAME] as [Name_BNAME],
// [TELF1] as [Telephone_TELF1],
// [MAHZA] as [Number of contacts_MAHZA],
// [MAHDT] as [Last contact date_MAHDT],
// [KOKRS] as [Controlling Area_KOKRS],
// [PS_PSP_PNR] as [WBS Element_PS_PSP_PNR],
// [KKBER] as [Credit control area_KKBER],
// [KNKLI] as [Credit account_KNKLI],
// [GRUPP] as [Cust.cred.group_GRUPP],
// [SBGRP] as [Credit rep.group_SBGRP],
// [CTLPC] as [Risk category_CTLPC],
// [CMWAE] as [Currency_CMWAE],
// [CMFRE] as [Release date_CMFRE],
// [CMNUP] as [Next check_CMNUP],
// [CMNGV] as [Next date_CMNGV],
// [AMTBL] as [Rel. credit value_AMTBL],
// [HITYP_PR] as [HierarchyTypePricing_HITYP_PR],
// [ABRVW] as [Usage_ABRVW],
// [ABDIS] as [MRP for DlvSchType_ABDIS],
// [OBJNR] as [Object no.header_OBJNR],
// [BUKRS_VF] as [CCode to be billed_BUKRS_VF],
// [TAXK1] as [Alt.tax classific._TAXK1],
// [TAXK2] as [TaxClass2-Cust._TAXK2],
// [TAXK3] as [TaxClass3-Cust._TAXK3],
// [TAXK4] as [TaxClass4-Cust._TAXK4],
// [TAXK5] as [TaxClass5-Cust._TAXK5],
// [TAXK6] as [TaxClass6-Cust._TAXK6],
// [TAXK7] as [TaxClass7-Cust._TAXK7],
// [TAXK8] as [TaxClass8-Cust._TAXK8],
// [TAXK9] as [TaxClass9-Cust._TAXK9],
// [XBLNR] as [Reference_XBLNR],
// [ZUONR] as [Assignment_ZUONR],
// [VGTYP] as [Preceding doc.categ._VGTYP],
// [KALSM_CH] as [Search procedure_KALSM_CH],
// [AGRZR] as [Accrual period_AGRZR],
// [QMNUM] as [Notification_QMNUM],
// [VBELN_GRP] as [Master contract_VBELN_GRP],
// [SCHEME_GRP] as [Group Ref. Procedure_SCHEME_GRP],
// [ABRUF_PART] as [Check partner auth._ABRUF_PART],
// [ABHOD] as [Pick up date_ABHOD],
// [ABHOV] as [Pick up time_ABHOV],
// [ABHOB] as [Pick up time_ABHOB],
// [RPLNR] as [Paym.card plan no._RPLNR],
// [VZEIT] as [Requested dely time_VZEIT],
// [STCEG_L] as [Tax dest. country_STCEG_L],
// [LANDTX] as [Tax depart. country_LANDTX],
// [XEGDR] as [EU triangular deal_XEGDR],
// [ENQUEUE_GRP] as [_ENQUEUE_GRP],
// [DAT_FZAU] as [CmlQtyDate_DAT_FZAU],
// [FMBDAT] as [Material Avail. Date_FMBDAT],
// [VSNMR_V] as [Version_VSNMR_V],
// [CONT_DG] as [Contains DG_CONT_DG],
// [PROLI] as [DG mgmt profile_PROLI],
// [MTLAUR] as [Target Incom_MTLAUR],
// [PHASE] as [Sales Phase_PHASE],
// [HANDLE] as [Int.ID_HANDLE],
// [KALCD] as [Proc. Camp.Determin._KALCD],
// [LOGSYSB] as [Logical system_LOGSYSB],
// [CRM_GUID] as [Char 70_CRM_GUID],
// [SWENR] as [Business entity_SWENR],
// [SMENR] as [Unit Number_SMENR],
// [STAGE] as [Construction stage_STAGE],
// [HB_CONT_REASON] as [Contingency reason_HB_CONT_REASON],
// [HB_EXPDATE] as [Expiration date_HB_EXPDATE],
// [HB_RESDATE] as [Resolution date_HB_RESDATE],
// [MULTI] as [Multiple Promotions_MULTI],
// [ZZDT_DOORHANG] as [Date of Door Hanger_ZZDT_DOORHANG],
// [ZZDT_POSTCARD] as [Date of Post Card Mailing_ZZDT_POSTCARD],
// [ZZDT_MAILED] as [Date Mailed_ZZDT_MAILED],
// [ZZDT_FOLLOWUP] as [Follow-Up Mailing Date_ZZDT_FOLLOWUP];

SQL Select * FROM VBAK where GUEBG >= '$(vYear)' and AUART = '$(vDocument)';

store VBAK into qvd\VBAK.qvd;

//**********************************************************************************
// Table: VBAP - Sales Order Line Item Detail
//**********************************************************************************

[VBAPTEMP]:
SQL Select *
FROM VBAP
WHERE VBELN in
(Select VBELN from VBAK where GUEBG >= '$(vYear)' and AUART = '$(vDocument)');

[VBAP_Temp]: // Sales Document: Item Data
Load
[VBELN] as [%SalesOrderDocument_Key],
[VBELN] & '/' & [POSNR] as [%SalesOrderDocumentItem_Key],
[MATNR] as [Material_MATNR],
[WERKS] as [Plant_WERKS],
[NETWR] as [Net value_NETWR],
[GSBER] as [Business Area_GSBER],
[KOSTL] as [Cost Center_KOSTL],
[POSNR] as [Sales Document Item_POSNR],
[POSAR] as [Item type_POSAR],
[ABGRU] as [Reason for rejection_ABGRU],
[KWMENG] as [Order Quantity_KWMENG],
[PRCTR] as [Profit Center_PRCTR],
[NETPR] as [Net price_NETPR],
[KZWI1] as [Subtotal 1_KZWI1],
[KZWI2] as [Subtotal 2_KZWI2],
[KZWI3] as [Subtotal 3_KZWI3],
[KZWI4] as [Subtotal 4_KZWI4],
[KZWI5] as [Subtotal 5_KZWI5],
[KZWI6] as [Subtotal 6_KZWI6],
[STCUR] as [Exchange rate stats._STCUR]
// [VBELN] as [Sales Document_VBELN],
// [WAERK] as [Document Currency_WAERK],
// [SPART] as [Division_SPART],
// [KPEIN] as [Pricing unit_KPEIN],
// [KMEIN] as [Unit of measure_KMEIN],
// [ERNAM] as [Created by_ERNAM],
// [ERZET] as [Time_ERZET],
// [ERDAT] as [Created on_ERDAT],
// [AEDAT] as [Changed on_AEDAT],
// [BRGEW] as [Gross weight_BRGEW],
// [NTGEW] as [Net weight_NTGEW],
// [LGORT] as [Storage Location_LGORT],
// [MEINS] as [Base Unit of Measure_MEINS],
// [VRKME] as [Sales unit_VRKME],
// [UMVKZ] as [Numerator_UMVKZ],
// [UMVKN] as [Denominator_UMVKN],
// [VBELV] as [Originating document_VBELV],
// [POSNV] as [Item_POSNV],
// [VGBEL] as [Reference document_VGBEL],
// [VGPOS] as [Reference item_VGPOS],
// [PSTYV] as [Item category_PSTYV],
// [VSTEL] as [Shipping Point/Receiving Pt_VSTEL],
// [AUFNR] as [Order_AUFNR],
// [STAFO] as [Update group (stats)_STAFO],
// [KNUMA_PI] as [Promotion_KNUMA_PI],
// [KNUMA_AG] as [Sales deal_KNUMA_AG],
// [PS_PSP_PNR] as [WBS Element_PS_PSP_PNR],
// [VGTYP] as [Preceding doc.categ._VGTYP],
// [MATWA] as [Material entered_MATWA],
// [PMATN] as [Pricing Ref. Matl_PMATN],
// [CHARG] as [Batch_CHARG],
// [MATKL] as [Material Group_MATKL],
// [ARKTX] as [Description_ARKTX],
// [LFREL] as [Itm relev.for deliv._LFREL],
// [FKREL] as [Relevant for Billing_FKREL],
// [UEPOS] as [Higher-level item_UEPOS],
// [GRPOS] as [Alternative to item_GRPOS],
// [PRODH] as [Product hierarchy_PRODH],
// [ZWERT] as [OA Target Value_ZWERT],
// [ZMENG] as [Target quantity_ZMENG],
// [ZIEME] as [Target quantity UoM_ZIEME],
// [UMZIZ] as [Conversion factor_UMZIZ],
// [UMZIN] as [Conversion factor_UMZIN],
// [SMENG] as [Scale quantity_SMENG],
// [ABLFZ] as [Rounding qty_ABLFZ],
// [ABDAT] as [Reconciliation Date_ABDAT],
// [ABSFZ] as [Absolute deviation_ABSFZ],
// [POSEX] as [Purchase order item_POSEX],
// [KDMAT] as [Customer Material_KDMAT],
// [KBVER] as [Deviation in percent_KBVER],
// [KEVER] as [Deviation days_KEVER],
// [VKGRU] as [Repair: Classification of Items_VKGRU],
// [VKAUS] as [Usage_VKAUS],
// [GRKOR] as [Delivery group_GRKOR],
// [FMENG] as [Quantity is Fixed_FMENG],
// [UEBTK] as [Unlimited tolerance_UEBTK],
// [UEBTO] as [Overdeliv. Tolerance_UEBTO],
// [UNTTO] as [Underdel. Tolerance_UNTTO],
// [FAKSP] as [Billing block_FAKSP],
// [ATPKZ] as [Replacement part_ATPKZ],
// [RKFKF] as [Billing meth. CO/PPC_RKFKF],
// [ANTLF] as [Max.Part.Deliveries_ANTLF],
// [KZTLF] as [Part.dlv./item_KZTLF],
// [CHSPL] as [Batch split allowed_CHSPL],
// [LSMENG] as [Required deliv. qty_LSMENG],
// [KBMENG] as [Cumul.confirmed qty_KBMENG],
// [KLMENG] as [Cumul.confirmed qty_KLMENG],
// [GEWEI] as [Weight unit_GEWEI],
// [VOLUM] as [Volume_VOLUM],
// [VOLEH] as [Volume unit_VOLEH],
// [VOREF] as [Complete reference_VOREF],
// [UPFLU] as [Update document flow_UPFLU],
// [ERLRE] as [Completion rule_ERLRE],
// [LPRIO] as [Delivery Priority_LPRIO],
// [ROUTE] as [Route_ROUTE],
// [STKEY] as [BOM origin_STKEY],
// [STDAT] as [BOM key date_STDAT],
// [STLNR] as [Bill of material_STLNR],
// [STPOS] as [Bill of material item number_STPOS],
// [AWAHR] as [Order probability_AWAHR],
// [TAXM1] as [Tax classification_TAXM1],
// [TAXM2] as [Tax classification_TAXM2],
// [TAXM3] as [Tax classification_TAXM3],
// [TAXM4] as [Tax classification_TAXM4],
// [TAXM5] as [Tax classification_TAXM5],
// [TAXM6] as [Tax classification_TAXM6],
// [TAXM7] as [Tax classification_TAXM7],
// [TAXM8] as [Tax classification_TAXM8],
// [TAXM9] as [Tax classification_TAXM9],
// [VBEAF] as [Fixed process. time_VBEAF],
// [VBEAV] as [Var. processing time_VBEAV],
// [VGREF] as [Preceding document resulted from ref._VGREF],
// [SHKZG] as [Returns_SHKZG],
// [SKTOF] as [Cash discount_SKTOF],
// [MTVFP] as [Availability check_MTVFP],
// [SUMBD] as [Requirements totals_SUMBD],
// [KONDM] as [Material pricing grp_KONDM],
// [KTGRM] as [Acct assignment grp_KTGRM],
// [BONUS] as [Volume rebate group_BONUS],
// [PROVG] as [Commission group_PROVG],
// [EANNR] as [EAN number_EANNR],
// [PRSOK] as [Pricing_PRSOK],
// [BWTAR] as [Valuation Type_BWTAR],
// [BWTEX] as [Separate valuation_BWTEX],
// [XCHPF] as [Batch management_XCHPF],
// [XCHAR] as [Batch management_XCHAR],
// [LFMNG] as [Minimum delivery qty_LFMNG],
// [WAVWR] as [Cost_WAVWR],
// [EAN11] as [EAN/UPC_EAN11],
// [FIXMG] as [Fixed date and qty_FIXMG],
// [MVGR1] as [Material group 1_MVGR1],
// [MVGR2] as [Material group 2_MVGR2],
// [MVGR3] as [Material group 3_MVGR3],
// [MVGR4] as [Material group 4_MVGR4],
// [MVGR5] as [Material group 5_MVGR5],
// [KMPMG] as [Component quantity_KMPMG],
// [SUGRD] as [Sustitution Reason_SUGRD],
// [SOBKZ] as [Special Stock_SOBKZ],
// [VPZUO] as [Allocation indicat._VPZUO],
// [PAOBJNR] as [Profitab. Segmt No._PAOBJNR],
// [VPMAT] as [Planning material_VPMAT],
// [VPWRK] as [Planning plant_VPWRK],
// [PRBME] as [Product group unit_PRBME],
// [UMREF] as [Cnversion_UMREF],
// [KNTTP] as [Acct Assignment Cat._KNTTP],
// [KZVBR] as [Consumption_KZVBR],
// [SERNR] as [BOM explosion number_SERNR],
// [OBJNR] as [Object no.Item_OBJNR],
// [ABGRS] as [Results Analysis Key_ABGRS],
// [BEDAE] as [Requirements type_BEDAE],
// [CMPRE] as [Credit price_CMPRE],
// [CMTFG] as [Partial release_CMTFG],
// [CMPNT] as [Credit funct. active_CMPNT],
// [CMKUA] as [Cred.data exch.rate_CMKUA],
// [CUOBJ] as [Configuration_CUOBJ],
// [CUOBJ_CH] as [Internal object no._CUOBJ_CH],
// [CEPOK] as [Expected price_CEPOK],
// [KOUPD] as [Condition update_KOUPD],
// [SERAIL] as [Serial no. profile_SERAIL],
// [ANZSN] as [No.serial numbers_ANZSN],
// [NACHL] as [No GR posted by cst._NACHL],
// [MAGRV] as [Matl Grp Pack.Matls_MAGRV],
// [MPROK] as [Manual price_MPROK],
// [PROSA] as [Mat.determ.active_PROSA],
// [UEPVW] as [Usage of HL item_UEPVW],
// [KALNR] as [Cost Estimate Number_KALNR],
// [KLVAR] as [Costing Variant_KLVAR],
// [SPOSN] as [Item Number_SPOSN],
// [KOWRR] as [Statistical value_KOWRR],
// [STADAT] as [Statistics date_STADAT],
// [EXART] as [Bus.transaction type_EXART],
// [PREFE] as [Preference auth._PREFE],
// [KNUMH] as [No.cond.rec. batch_KNUMH],
// [CLINT] as [Internal class no._CLINT],
// [CHMVS] as [Quantity proposal_CHMVS],
// [STLTY] as [BOM category_STLTY],
// [STLKN] as [Item node_STLKN],
// [STPOZ] as [Counter_STPOZ],
// [STMAN] as [Inconsistent confign_STMAN],
// [ZSCHL_K] as [Overhead key_ZSCHL_K],
// [KALSM_K] as [Costing Sheet_KALSM_K],
// [KALVAR] as [Costing Variant_KALVAR],
// [KOSCH] as [Product allocation_KOSCH],
// [UPMAT] as [MnItm PrcRefMatl_UPMAT],
// [UKONM] as [MnItem MatPricingGrp_UKONM],
// [MFRGR] as [Material freight grp_MFRGR],
// [PLAVO] as [PlanDelSchedInstruct_PLAVO],
// [KANNR] as [Sequence number_KANNR],
// [CMPRE_FLT] as [Credit price_CMPRE_FLT],
// [ABFOR] as [Paymt guarantee form_ABFOR],
// [ABGES] as [Guaranteed_ABGES],
// [J_1BCFOP] as [CFOP_J_1BCFOP],
// [J_1BTAXLW1] as [ICMS law_J_1BTAXLW1],
// [J_1BTAXLW2] as [IPI law_J_1BTAXLW2],
// [J_1BTXSDC] as [Tax code_J_1BTXSDC],
// [WKTNR] as [Value contract no._WKTNR],
// [WKTPS] as [Value contract item_WKTPS],
// [SKOPF] as [Module_SKOPF],
// [KZBWS] as [Spec. Stk Valuation_KZBWS],
// [WGRU1] as [Material group 1_WGRU1],
// [WGRU2] as [Material group 2_WGRU2],
// [KZFME] as [Leading UoM_KZFME],
// [LSTANR] as [Free goods delivery control_LSTANR],
// [TECHS] as [Standard Variant_TECHS],
// [MWSBP] as [Tax amount_MWSBP],
// [BERID] as [MRP Area_BERID],
// [FISTL] as [Funds Center_FISTL],
// [FKBER] as [Functional Area_FKBER],
// [GRANT_NBR] as [Grant_GRANT_NBR],
// [FERC_IND] as [Regulatory indicator_FERC_IND],
// [FONDS] as [Fund_FONDS],
// [J_1BTAXLW4] as [COFINS Law_J_1BTAXLW4],
// [J_1BTAXLW5] as [PIS Law_J_1BTAXLW5],
// [J_1BTAXLW3] as [ISS Law_J_1BTAXLW3],
// [PCTRF] as [Profit Center Billing_PCTRF],
// [LOGSYS_EXT] as [Logical system_LOGSYS_EXT]
RESIDENT VBAPTEMP
WHERE exists([%SalesOrderDocument_Key], [VBELN]);

[VBAP_Temp]:
Left Join (VBAP_Temp)
LOAD [%SalesOrderDocument_Key]
RESIDENT VBAK;

//DROP TABLE [VBAK];
//DROP TABLE VBAPTEMP, VBAP_Temp;

Left Join Problems

Thu, 14 Jan 2010 21:57:42 GMT

You use this piece:


[VBAP_Temp]:
Left Join (VBAP_Temp)
 LOAD [%SalesOrderDocument_Key]
RESIDENT VBAK;

Try this


Left Join (VBAP_Temp)
LOAD *
RESIDENT VBAK;

It joins VBAK and VBAP_Temp into table VBAP_Temp.

Left Join Problems

tmumaw — Fri, 15 Jan 2010 16:55:30 GMT

Thanks Mark. That fixed it.

Left Join Problems

Fri, 15 Jan 2010 17:30:52 GMT

Then you should verify my answer instead of your own LOL

Glad it worked.

topic Left Join Problems in QlikView

Left Join Problems

Left Join Problems

Left Join Problems

Left Join Problems