topic Where Exists... in QlikView

Where Exists...

tmumaw — Mon, 18 Jan 2010 15:35:42 GMT

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]);

store VBAK into qvd\VBAK.qvd;

Where Exists...

Mon, 18 Jan 2010 15:46:44 GMT

Hi,

so far I see you did not have a field ZORDER.

Rainer

Where Exists...

tmumaw — Mon, 18 Jan 2010 15:55:08 GMT

ZORDER is in the first table ZTRPST

Where Exists...

Mon, 18 Jan 2010 15:59:02 GMT

You renamed it: [ZORDER] as [Order Number_ZORDER],

Rainer

Where Exists...

tmumaw — Mon, 18 Jan 2010 16:03:28 GMT

I changed my sql statement to look like this and I get a script line error on it: Should they be reversed?

Thanks

SQL Select * FROM VBAK WHERE exists([%SalesOrderDocument_Key], [Order Number_ZORDER]);

Where Exists...

johnw — Tue, 19 Jan 2010 01:55:44 GMT

I think the basic problem is that you're using a QlikView function inside of an SQL statement. Your DBMS has no idea what to do with a QlikView function. In other words, the exists() goes in the LOAD statement, not the in SQL statement.

LOAD
...
WHERE EXISTS(the field you loaded previously, the field you are now loading)
;
SQL SELECT
...
;

Where Exists...

Tue, 19 Jan 2010 05:58:19 GMT

hello

the solution to your problem would be you should load both the fields before using them in where exists . What i mean by this is you should load the table ZTRPST with field[ZORDER] and then load the next table VBAK with [VBELN] field and with this Load statement ,use

Where exists([ZORDER],[VBELN])

Where Exists...

Tue, 19 Jan 2010 06:32:13 GMT

Hi Thom
Are you trying to extract from SAP ? If so how many records do you have in the ODS's you are extracting from ?
I have had some errors too ( http://community.qlik.com/forums/p/24594/93942.aspx#93942 ) trying to do similar restrictions on extract.
Greenee

Where Exists...

tmumaw — Thu, 21 Jan 2010 17:47:19 GMT

Yes. I am trying to extract sales orders from SAP. In VBAK we have 16 million rows and on VBAP we have 49 million rows. I am able to extract them but it takes way too long.

Where Exists...

tmumaw — Thu, 21 Jan 2010 17:48:22 GMT

Silky,

Thanks. What I needed to do was create temp tables and do the where exists against them.

Thom

Where Exists...

Sun, 19 Dec 2010 20:51:24 GMT

Hi John,

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?

I hope my explanation is not too confused,

kind regards,
Lenka

Where Exists...

johnw — Mon, 20 Dec 2010 19:39:46 GMT


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))
;

Where Exists...

Mon, 03 Jan 2011 13:55:41 GMT

Hi John,

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.

Thanks again for your help,
best wishes,

Lenka

Re: Where Exists...

Mon, 23 Jan 2012 19:21:37 GMT

John et al,

Your example works as advertised 😉

For anyone who uses this. The editor will show an error on the following line:

LOAD concat(distinct `I_Vin_Last_8`,chr(39)&','&chr(39)) as KeyValues

The editor will show the error line on the first chr(39) but it runs and populated the variable correctly regardless of the little red squiggles ,

The In() with the contents of the variable and the query ran successfully,

As an FYI using in for a long list is going to be very slow but i have no ideawhat the cut off recommendation might be or what alternative may exist.