Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld online is next week! REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
tmumaw
Specialist II
Specialist II

Logic help

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

1 Solution

Accepted Solutions
Gysbert_Wassenaar

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


talk is cheap, supply exceeds demand

View solution in original post

7 Replies
Gysbert_Wassenaar

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


talk is cheap, supply exceeds demand
tmumaw
Specialist II
Specialist II
Author

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
Partner
Partner

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
Partner
Partner

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

tmumaw
Specialist II
Specialist II
Author

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,

Great dreamer's dreams never fulfilled, they are always transcended.
tmumaw
Specialist II
Specialist II
Author

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