topic Logic help in QlikView

Logic help

tmumaw — Thu, 30 Aug 2018 11:05:07 GMT

Morning everyone,

I need a little help with some logic. It appears it's ignoring the '00000000' because it's not setting it to a 'N'. Any ideas what I might be missing on this. I have tried it with and without quotes. thanks

[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],
[VBELN],
[WERKS] as [Plant],
[NETWR] as [Net Value],
[POSNR] as [Contract Line],
[MATKL] as [%MaterialGroup_Key],
[ZMENG] as [QTY],
[KZWI1] as [Subtotal 1],
[KZWI2] as [Subtotal 2],
[KZWI3] as [Subtotal 3],
[KZWI4] as [Subtotal 4],
[KZWI5] as [Subtotal 5],
[KZWI6] as [Subtotal 6],
[PRCTR] as [Profit Center],
PS_PSP_PNR
RESIDENT VBAPTEMP
WHERE exists([%ContractDocument_Key], [VBELN])
;

VBAP_Temp:
Load *,
If(PS_PSP_PNR = '00000000','N','Y') as WBS_SW
Resident Temp;

Drop Table Temp;

Left join (VBAK)
Load *
Resident VBAP_Temp
Where WBS_SW = 'Y';

Re: Logic help

Gysbert_Wassenaar — Thu, 30 Aug 2018 11:14:07 GMT

Try loading that field explicitly as text: Text(PS_PSP_PNR) as PS_PSP_PNR

Or try testing it being equal to 0: If(PS_PSP_PNR = 0,'N','Y') as WBS_SW

Re: Logic help

tmumaw — Thu, 30 Aug 2018 15:59:45 GMT

Why would I not store only records with a 'Y' in WBS_SW/

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

SQL Select * from VBAK where (AUART = 'ZRRC' or AUART = 'ZBPC' or AUART = 'ZRPS' or AUART = 'ZBCS') and GUEEN >= '$(vYearEnd)'
;

[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],
[VBELN],
[WERKS] as [Plant],
[NETWR] as [Net Value],
[POSNR] as [Contract Line],
[MATKL] as [%MaterialGroup_Key],
[ZMENG] as [QTY],
[KZWI1] as [Subtotal 1],
[KZWI2] as [Subtotal 2],
[KZWI3] as [Subtotal 3],
[KZWI4] as [Subtotal 4],
[KZWI5] as [Subtotal 5],
[KZWI6] as [Subtotal 6],
[PRCTR] as [Profit Center],
PS_PSP_PNR,
If(PS_PSP_PNR = 00000000,'N','Y') as WBS_SW
RESIDENT VBAPTEMP
WHERE exists([%ContractDocument_Key], [VBELN])
;

Left join (VBAKX)
Load *
Resident VBAP_Temp
;
Drop table VBAP_Temp;
Drop table VBAPTEMP;

VBAK:
Load *
Resident VBAKX
Where WBS_SW = 'Y'
;
Store VBAKX into QVD\VBAKX.QVD;

Re: Logic help

wade12 — Thu, 30 Aug 2018 16:10:22 GMT

it is mostly likely a data type issue ... you are asking qlik to compare a number/interger to a text/string.

you need the data types to be the same in order to get accurate results from the if test condition.

try changng field to be of data type string/text

or change = '00000' to = 0

Re: Logic help

nsetty — Fri, 31 Aug 2018 04:33:30 GMT

TRY If(TEXT(PS_PSP_PNR) = '00000000','N','Y') as WBS_SW

Re: Logic help

tmumaw — Fri, 31 Aug 2018 10:40:10 GMT

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],
[VBELN],
[WERKS] as [Plant],
[NETWR] as [Net Value],
[POSNR] as [Contract Line],
[MATKL] as [%MaterialGroup_Key],
[ZMENG] as [QTY],
[KZWI1] as [Subtotal 1],
[KZWI2] as [Subtotal 2],
[KZWI3] as [Subtotal 3],
[KZWI4] as [Subtotal 4],
[KZWI5] as [Subtotal 5],
[KZWI6] as [Subtotal 6],
[PRCTR] as [Profit Center],
[PS_PSP_PNR] as WBS,
If(PS_PSP_PNR <> '00000000','Y','N') as WBS_SW
RESIDENT VBAPTEMP
WHERE exists([%ContractDocument_Key], [VBELN])
;

Left join (VBAKX)
Load *
Resident VBAP_Temp
;

VBAK:
Load *
Resident VBAKX
where WBS_SW = 'Y'
;

Store VBAKX into QVD\VBAK.QVD;

Store VBAP_Temp into QVD\VBAP.QVD;

Drop tables VBAKX, VBAP_Temp
;
Exit SCRIPT;

I got past the part trying to assign the 'Y' and 'N' but after I do the left join and read the VBAKX in as resident it's like it's ignoring the where clause and writing everything out to VBAK. Any ideas? Thanks

Re: Logic help

PrashantSangle — Fri, 31 Aug 2018 10:48:02 GMT

write noconcatenate before VBVK: table.

Regards,

Re: Logic help

tmumaw — Fri, 31 Aug 2018 12:18:20 GMT

Thanks Prashant....that completed my script and everything is running great.