Logic help

[tmumaw]

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

[Gysbert_Wassenaar · Accepted solution]

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

[tmumaw]

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;

[wade12]

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

[nsetty]

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

[tmumaw]

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

[PrashantSangle]

write noconcatenate before VBVK: table.

Regards,

[tmumaw]

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