Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
tmumaw
Valued Contributor

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

Tags (1)
1 Solution

Accepted Solutions
Highlighted
MVP & Luminary
MVP & Luminary

Re: Logic help

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
Highlighted
MVP & Luminary
MVP & Luminary

Re: Logic help

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

Highlighted
tmumaw
Valued Contributor

Re: Logic help

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;

Highlighted
Partner
Partner

Re: Logic help

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

Highlighted
Partner
Partner

Re: Logic help

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

Highlighted
tmumaw
Valued Contributor

Re: Logic help

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

Highlighted

Re: Logic help

write noconcatenate before VBVK: table.

Regards,

Great dreamer's dreams never fulfilled, they are always transcended.
Highlighted
tmumaw
Valued Contributor

Re: Logic help

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