Logic Help

tmumaw — Wed, 24 Jan 2018 17:55:45 GMT

Need a little help with my logic. The part highlighted in green works fine, it's what's in red that don't. It's the last part that doesn't. Can anyone see anything I am doing wrong? Thanks

[VBAKX]:
Load
KUNNR & VKORG & VTWEG AS %KEY_KNVV,
[VBELN],
[VBELN] as [%ContractDocument_Key],
Right([VBELN],8) as [Contract_ID],
[VKORG] as [%SalesOrganisation_Key],
[ERNAM] as [Created by],
[ERDAT] as [Contract Create Date],
[VTWEG] as [%DistributionChannel_Key],
[VBTYP] as [Doc Cat],
[AUART] as [Sales Document Type],
[KUNNR] as [Ship To],
[VKBUR] as [SalesOffice_Key],
[VKGRP] as [%SalesGroup_Key],
[AUDAT] as [Document Date],
[NETWR] as [Contract Net Value],
[WAERK] as [Doc Curr],
[GUEBG] as [Valid Frm Date],
year(GUEBG) as Valid_Year,
[GUEEN] as [Valid To Date],
[PS_PSP_PNR] as [WBS Element],
[ZZWBS],
If(Mid(ZZWBS,1,1) = 'W',1,0) as WBS_SW
;

SQL Select * from VBAK where (AUART = 'ZRRC' or AUART = 'ZBPC' or AUART = 'ZRPS' or AUART = 'ZBCS') //and (ZZWBS <> '00000000' or ZZWBS <> ' ')
and GUEEN >= '$(vYearEnd)'
;
//Store VBAK into QVD\VBAKX.QVD
;

NoConcatenate
VBAK:
Load *
Resident VBAKX
Where WBS_SW = '1'
;
Store VBAK into QVD\VBAK.QVD
;
Drop table VBAKX;
[VBAPTEMP]:
SQL Select *
FROM VBAP
WHERE VBELN in (Select VBELN from VBAK where (AUART = 'ZRRC' or AUART = 'ZBPC' or AUART = 'ZRPS' or AUART = 'ZBPS') and
GUEEN >= '$(vYearEnd)' )
;

[VBAP_Temp]: // Sales Document: Item Data
Load
[VBELN] & Right([POSNR],4) as [%ContractItemKey],
[VBELN] & Right([POSNR],4) as [%ContractItemKeySumm],
[VBELN] & '/' & [POSNR] as [%ContractDocumentItem_Key],
[MATNR] as [%MaterialNumber_Key],
[SPART] as [Div],
[KMEIN] as [UOM],
[MEINS] as [Base UOM],
[VRKME] as [Sales Unit],
[VBELV] as [Originating Doc],
[VGBEL] as [Quote],
[VGPOS] as [Quote Line],
[PSTYV] as [Item Category],
[ERDAT] as [Line Create Date],
[VBELN],
[WERKS] as [Plant],
[NETWR] as [Net Value],
[WAERK] as [DocCurr],
[KOSTL] as [Cost Center],
[PS_PSP_PNR] as [WBS Element Line],
[POSNR] as [Contract Line],
[MATKL] as [%MaterialGroup_Key],
[ABGRU] as [Reject Reason],
[ZMENG] as [QTY],
[FAKSP] as [Billing Block Line],
[TAXM1] as [Tax Class 1],
[TAXM2] as [Tax Class 2],
[TAXM3] as [Tax Class 3],
[TAXM4] as [Tax Class 4],
[TAXM5] as [Tax Class 5],
[TAXM6] as [Tax Class 6],
[TAXM7] as [Tax Class 7],
[TAXM8] as [Tax Class 8],
[TAXM9] as [Tax Class 9],
[KZWI1] as [Subtotal 1],
[KZWI2] as [Subtotal 2],
[KZWI3] as [Subtotal 3],
[KZWI4] as [Subtotal 4],
[KZWI5] as [Subtotal 5],
[KZWI6] as [Subtotal 6],
[STCUR] as [Exchange Rate],
[PRCTR] as [Profit Center],
[MVGR1] as [MatGrp1],
[MVGR2] as [MatGrp2],
[MVGR3] as [MatGrp3],
[MVGR4] as [MatGrp4],
[MVGR5] as [MatGrp5],
[OBJNR] as [Object No.Item],
[VKAUS] as [TPLR],
[FKBER] as [Functional Area],
[ZZONNEXTRENEWAL] as [On Next Renewal],
[ZZRENEWALCYCLE] as [Renewal Cycle],
[ZZNEXTRNWLYEAR] as [Next Renewal Year],
[ZZNEXTRNWLCANC] as [Next Renewal Cancel Code],
[ZZFINALPROPAMT] as [Final Proposed Amount],
[ZZFINALPROPPO] as [Final Proposed PO_DNP],
[ZZFINALPROCANC] as [Final Proposed Cancel Code]
RESIDENT VBAPTEMP
WHERE exists([%ContractDocument_Key], [VBELN])
;

Left join (VBAK)
Load *
Resident VBAP_Temp;

Store VBAK into QVD\VBAK.QVD;

Store VBAP_Temp into QVD\VBAP.QVD;

[VBKDTEMP]:
SQL Select *
FROM VBKD
WHERE VBELN in (Select VBELN from VBAK where (AUART = 'ZRRC' or AUART = 'ZBPC' or AUART = 'ZRPS' or AUART = 'ZBPS') and
PRSDT >= '$(vYearEnd)')
;

[VBKD_Temp]: // Sales Document: Business Data
Load
[VBELN] as [SD Doc._VBELN.VBKD],
[VBELN] & Right([POSNR],4) as [%ContractItemKey],
[PERFK] as [Invoice Periods]
RESIDENT VBKDTEMP
WHERE exists([%ContractDocument_Key], [VBELN])
;

Store VBKD_Temp into QVD\VBKD.QVD;

[VBPA_HTEMP]:

SQL Select * from VBPA where POSNR = '000000'
AND VBELN in (Select VBELN from VBAK where (AUART = 'ZRRC' or AUART = 'ZBPC') and
GUEEN >= '$(vYearEnd)' );
[VBPA_H]:
Load
[VBELN] as [%ContractDocument_Key],
[KUNNR] as [H_Customer],
[PARVW] as [H_Partner],
[PERNR] as [H_Sales Associate]
RESIDENT [VBPA_HTEMP]
WHERE exists([%ContractDocument_Key], [VBELN]);

store VBPA_H into QVD\VBPA_Partners_H.qvd;

drop table VBPA_H, [VBPA_HTEMP];

//Drop table VBAK;

Drop Table VBKD_Temp;

Drop table VBAP_Temp;

Drop table VBAPTEMP;

DROP TABLE VBKDTEMP;

[PRPSTEMP]:
SQL Select *
FROM PRPS
WHERE POSID in (Select ZZWBS from VBAK)
;

[PRPS]: // WBS (Work Breakdown Structure) Element Master Data
Load POSID,
OBJNR as PRPS_OBJNR
RESIDENT PRPSTEMP
WHERE exists([ZZWBS], [POSID])
;
STORE * FROM [PRPS] INTO QVD\PRPS.QVD;

DROP TABLES PRPSTEMP, VBAK;

[COVPTEMP]:
SQL Select *
FROM COVP
WHERE OBJNR in (Select PRPS_OBJNR from PRPS)
;

[COVP]: // Generated Table for View COVP
Load
[MATNR] as [MaterialMATNR],
[BUKRS] as [Company Code_BUKRS],
[KURST] as [Exch. Rate Type_KURST],
[WERKS] as [PlantWERKS],
[MEINH] as [Unit of Measure_MEINH],
[EBELN] as [Purchasing Doc._EBELN],
[GSBER] as [Business Area_GSBER],
[KOKRS] as [CO Area_KOKRS],
[QMNUM] as [Notification_QMNUM],
[PAOBJNR] as [Profit. segment_PAOBJNR],
[PERNR] as [Personnel No._PERNR],
[GJAHR] as [Fiscal Year_GJAHR],
[BELNR] as [Document Number_BELNR],
[USNAM] as [User Name_USNAM],
[BUDAT] as [Posting Date_BUDAT],
[CPUTM] as [Entered at_CPUTM],
[BLDAT] as [Document Date_BLDAT],
[AWTYP] as [Ref. Transactn_AWTYP],
[PARGB] as [Trdg part.BA_PARGB],
[VBUND] as [Trading Partner_VBUND],
[EBELP] as [Item_EBELP],
[ZEKKN] as [Seq.No.Acc.Ass._ZEKKN],
[DABRZ] as [Reference Date_DABRZ],
[GEBER] as [Fund_GEBER],
[FKBER] as [Functional Area_FKBER],
[WSDAT] as [Value date_WSDAT],
[AWORG] as [Referenc Org.Un_AWORG],
[OBJNR] as [Object number_OBJNR],
[WRTTP] as [Value Type_WRTTP],
[VERSN] as [Version_VERSN],
[GRANT_NBR] as [Grant_GRANT_NBR],
[SEGMENT] as [Segment_SEGMENT],
[KSTAR] as [Cost Element_KSTAR],
[LEDNR] as [Ledger_LEDNR],
[HRKFT] as [CO subkey_HRKFT],
[VRGNG] as [Bus.Transaction_VRGNG],
[PAROB] as [Partner Object_PAROB],
[PAROB1] as [Partner object_PAROB1],
[USPOB] as [Source Object_USPOB],
[BEKNZ] as [Dr/Cr indicator_BEKNZ],
[MVFLG] as [Qty Compl/Incom_MVFLG],
[SGTXT] as [Name_SGTXT],
[REFBZ] as [Posting row_REFBZ],
[ZLENR] as [Item_ZLENR],
[BW_REFBZ] as [Row in op. ver._BW_REFBZ],
[GKONT] as [Offsetting acct_GKONT],
[GKOAR] as [Offst.acct type_GKOAR],
[RBEST] as [PO Category_RBEST],
[ERLKZ] as [Completion ind._ERLKZ],
[BTRKL] as [Amount Class_BTRKL],
[OBJNR_N1] as [AuxAcctAsmnt_1_OBJNR_N1],
[OBJNR_N2] as [AuxAcctAssmt_2_OBJNR_N2],
[OBJNR_N3] as [AuxAcctAsmnt_3_OBJNR_N3],
[BELTP] as [Debit type_BELTP],
[SCOPE] as [Obj Class_SCOPE],
[LOGSYSO] as [Logical system_LOGSYSO],
[PKSTAR] as [Partner Cost El_PKSTAR],
[PBUKRS] as [Partner CoCode_PBUKRS],
[PFKBER] as [Partner FArea_PFKBER],
[PSCOPE] as [PartnerObjClass_PSCOPE],
[LOGSYSP] as [Logical system_LOGSYSP],
[BWSTRAT] as [Val.strategy_BWSTRAT],
[OBJNR_HK] as [Origin object_OBJNR_HK],
[TIMESTMP] as [Time created_TIMESTMP],
[PGEBER] as [Partner Fund_PGEBER],
[PGRANT_NBR] as [Partner Grant_PGRANT_NBR],
[PERIO] as [Period_PERIO],
[BUZEI] as [Posting row_BUZEI],
[WTGBTR] as [Value TranCurr_WTGBTR],
[WOGBTR] as [Value/Obj. Crcy_WOGBTR],
[WKGBTR] as [Val/COArea Crcy_WKGBTR],
[WKFBTR] as [FixValue COCurr_WKFBTR],
[PAGBTR] as [Price Variance_PAGBTR],
[PAFBTR] as [Price Var. Fxd_PAFBTR],
[MEGBTR] as [Total Quantity_MEGBTR],
[MEFBTR] as [Fixed Quantity_MEFBTR],
[MBGBTR] as [Total quantity_MBGBTR],
[MBFBTR] as [Fix qty entered_MBFBTR],
[MEINB] as [Posted UofMeas._MEINB],
[TWAER] as [Trans. Currency_TWAER],
[OWAER] as [Object Currency_OWAER],
[LOGSYSTEM] as LSL,
[PSEGMENT] as [Partner Segment_PSEGMENT],
[BLART] as [Document type_BLART],
[BLTXT] as [Doc.Header Text_BLTXT],
[AWSYS] as [Logical system_AWSYS],
[AWREF_REV] as [Rvrsl ref._AWREF_REV],
[AWORG_REV] as [Rvrsl org._AWORG_REV],
[STOKZ] as [Reversed_STOKZ],
[CPUDT] as [Created on_CPUDT],
[STFLG] as [Reversal doc._STFLG],
[SUMBZ] as [Line item total_SUMBZ],
[DELBZ] as [Del.line items_DELBZ],
[REFBZ_FI] as [FI Posting Item_REFBZ_FI],
[REFBN] as [Ref Document No_REFBN],
[REFBT] as [RefDocType_REFBT],
[PERAB] as [From Period_PERAB],
[PERBI] as [to_PERBI],
[REFBK] as [RefCompanyCode_REFBK],
[REFGJ] as [RefFiscalYear_REFGJ],
[ORGVG] as [Original BTran._ORGVG],
[VARNR] as [Screen variant_VARNR],
[KWAER] as [COarea currency_KWAER],
[CTYP1] as [Currency Type 1_CTYP1],
[CTYP2] as [Currency Type 2_CTYP2],
[CTYP3] as [Currency Type 3_CTYP3],
[CTYP4] as [Currency Type 4_CTYP4],
[ALEBZ] as [Post rows ext._ALEBZ],
[ALEBN] as [ALE: Org.DocNr_ALEBN],
[K_GJAHR] as [Fiscal Year_K_GJAHR],
[K_VERSN] as [Version_K_VERSN],
[K_VRGNG] as [Bus.Transaction_K_VRGNG],
[K_TIMESTMP] as [Time created_K_TIMESTMP]
Resident COVPTEMP
WHERE exists([PRPS_OBJNR], [OBJNR])
;
STORE * FROM [COVP] INTO QVD\COVP.QVD;

DROP TABLES COVPTEMP, PRPS, COVP;

Exit SCRIPT;

Here is my ERROR:

QVX_UNEXPECTED_END_OF_DATA: SQL_ERROR

Unknown column name "PRPS_OBJNR" field list. field

list. field list. field list.

[COVPTEMP]:

SQL Select *

FROM COVP

WHERE OBJNR in (Select PRPS_OBJNR from PRPS)

Re: Logic Help

petter — Wed, 24 Jan 2018 18:13:37 GMT

I think that if you change this:

[COVPTEMP]:
SQL Select *
FROM COVP
WHERE OBJNR in (Select PRPS_OBJNR from PRPS);

into this:

[COVPTEMP]:
SQL Select *
FROM COVP
WHERE OBJNR in (Select OBJNR from PRPS);

that it should work...

Re: Logic Help

tmumaw — Wed, 24 Jan 2018 19:19:23 GMT

Thanks Petter. My eyes were starting to run together and needed another set. It worked perfect.

topic Re: Logic Help in QlikView

Logic Help

Re: Logic Help

Re: Logic Help