With the following script I only want to extract records from VBAK where they exist in ZTRPST. The matching fields would be [ZORDER] from ZTRPST and [VBELN] from VBAK. I keep getting errors when I use the following stmt : SQL Select * FROM VBAK WHERE exists([%SalesOrderDocument_Key], [ZORDER]);
Thanks Thom
[ZTRPST]: // Time Reports - Spread Time Posted Load [ZORDER] & '/' & [ZACTIVITY] as [%SalesOrderDocumentItem_Key], [ZTICKNO] as [Ticket Number_ZTICKNO], [ZWDATE] as [Date in the week_ZWDATE], [ZEMPEQP] as [Indicator for employee or equipment_ZEMPEQP], [ZHOURS] as [Hours Registered_ZHOURS], [ZHOMCTR] as [Cost Center_ZHOMCTR], [ZORDER] as [Order Number_ZORDER], [ZACTIVITY] as [Activity_ZACTIVITY], [ZSTATUS] as [Job Status_ZSTATUS], [ZFLGDMEM] as [Status flag for Debit Memo creation_ZFLGDMEM], [ZOBJECT] as [Employee&Activity / Equip&Equip Type_ZOBJECT], [ZORDER1] as [Order Number_ZORDER1], [ZACTIVITY1] as [Activity_ZACTIVITY1], [ZFLGSERV] as [Status Flag for Service Order Creation_ZFLGSERV], [ZFLGCONF] as [Status Flag for Confirmations (IW41)_ZFLGCONF], [ZSERVORDTYPE] as [_ZSERVORDTYPE], [ZHOMCTR_SERVORD] as [Cost Center_ZHOMCTR_SERVORD], [ZSTORM] as [_ZSTORM]; SQL Select ZORDER ZTICKNO ZWDATE ZEMPEQP ZHOURS ZHOMCTR ZSTATUS ZACTIVITY ZFLGDMEM ZOBJECT ZORDER1 ZACTIVITY1 ZFLGSERV ZFLGCONF ZSERVORDTYPE ZHOMCTR_SERVORD ZSTORM from ZTRPST where ZWDATE >= '20090104' and ZEMPEQP = 'P' and ZORDER between '000040000000' and '000049999999' ; STORE * FROM [ZTRPST] INTO QVD\ZTRPST.QVD;
//DROP TABLE [ZTRPST];
//********************************************************************************** // 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)';
SQL Select * FROM VBAK WHERE exists([%SalesOrderDocument_Key], [ZORDER]);
I am facing the same problem, but I don't want to perform the whole select-statement before implementing the exists-clause, because the select statement would take ca. 1 hrs, even though I need only a few indexed datasets that would load in a matter of minutes. I can't use a WHERE-clause in this case, because the table is the CE40010 in SAP that connects only via a numbered key to another table - no way to use a 'created-after'-condition to restrict the load.
Is there any correct syntax in QlikView to use an SQL-equivalent of the exists-clause to restrict the data-load in the sql-select-statement?
lenkacfk wrote:Is there any correct syntax in QlikView to use an SQL-equivalent of the exists-clause to restrict the data-load in the sql-select-statement?
If you don't have too many values, you could turn them into a variable to plug into the SQL. Something along these lines, though I may of course have syntax or other errors:
[Table to Help Build our Variable]: LOAD concat(distinct SomeKeyField,chr(39)&','&chr(39)) as KeyValues RESIDENT [Some Existing Table] ; LET vKeyValues = chr(39) & peek('KeyValues') & chr(39) ; DROP TABLE [Table to Help Build our Variable] ; [Table from SQL]: LOAD ... ; SQL SELECT ... WHERE SomeKeyField IN($(vKeyFields)) ;
happy new year! And thanks for your suggestion, but meanwhile I've found out from the customer that he only updates that table twice a month, so I will just do a full reload then and spare myself the hassle with the WHERE-IN-statement. But it's good to know that that, at least, works in QlikView-SQL-syntax.
