topic Re: Unwanted Field in QlikView

Unwanted Field

oseimuduame — Wed, 25 Nov 2020 16:16:04 GMT

Good Afternoon,

Is it possible to Exclude a particular field from the Database, i tried doing it with WHERE NOT but it only excludes the name of the Field but showing other attributes of the field.

Thank You.

Re: Unwanted Field

vinieme12 — Mon, 24 Apr 2017 14:54:43 GMT

if you know what fields are to be loaded, then specify all fields in the LOAD statement

if you only know what fields are to be excluded then drop them after the table load

example

Drop fields fielda,field2,.....;

Re: Unwanted Field

oseimuduame — Mon, 24 Apr 2017 15:11:44 GMT

i will try that but lets take for example i have a field that contains Column that contains Mango, Apple, Orange and Ball, but i want to fetch all items except Ball.

Thats what i meant. How can i fetch all items and ecxlude just Ball from my selection.

i used WHERE NOT Description = 'the Name of the Item(BAll);',

Please i need assistance

Re: Unwanted Field

vinieme12 — Mon, 24 Apr 2017 15:13:16 GMT

WHERE Wildmatch(FieldName,'Ball')=0;

Re: Unwanted Field

oseimuduame — Mon, 24 Apr 2017 15:22:29 GMT

What i am getting in SQL Script Editor is Invalid Identifier.

Re: Unwanted Field

vinieme12 — Mon, 24 Apr 2017 15:33:43 GMT

please post the script you are using

Re: Unwanted Field

oseimuduame — Tue, 25 Apr 2017 08:30:17 GMT

Lemme show you what

Now from the above table i want to exclud the row thats holding LPG BUTANE and so that i will only have my Gas Refill.

Any idea how that can be done

Re: Unwanted Field

vinieme12 — Tue, 25 Apr 2017 08:39:10 GMT

in Qlikview LOAD statement

Load *

From XXX

WHERE WildMatch(ItemName,'LPG Butane')=0;

Re: Unwanted Field

vinieme12 — Tue, 25 Apr 2017 08:40:12 GMT

i think you are adding the where clause in the Select statement instead of Load , if you post your script i can tell you where to place it

Re: Unwanted Field

awhitfield — Tue, 25 Apr 2017 08:44:44 GMT

Where are you loading you r data from ?

Andy

Re: Unwanted Field

awhitfield — Tue, 25 Apr 2017 08:48:59 GMT

If its SQl, then you may need something like

select * from (yourtable) where (yourfield) not like '%butane%'

Andy

Re: Unwanted Field

oseimuduame — Tue, 25 Apr 2017 09:01:21 GMT

ORACLE

Re: Unwanted Field

vinieme12 — Tue, 25 Apr 2017 09:06:11 GMT

please post your script!

Re: Unwanted Field

oseimuduame — Tue, 25 Apr 2017 09:17:39 GMT

I think its working now but below is my script.

//items

SQL SELECT

TO_CHAR(n.ITEM_SID) AS "ITEM_SID",

n.ITEM_NO AS "ITEM_NO",

n.SBS_NO AS "SBS_NO",

n.ACTIVE AS "ITEM_ACTIVE",

n.VEND_CODE AS "VEND_CODE",

n.DCS_CODE AS "DCS_CODE",

n.DESCRIPTION1 AS "ITEM_DESCRIPTION1",

n.DESCRIPTION2 AS "ITEM_DESCRIPTION2",

n.DESCRIPTION3 AS "ITEM_DESCRIPTION3",

n.DESCRIPTION4 AS "ITEM_DESCRIPTION4",

n.ATTR AS "ITEM_ATTR",

n.SIZ AS "ITEM_SIZ",

n.ALU AS "ITEM_ALU",

u.UPC AS "ITEM_UPC",

n.UDF1_DATE AS "UDF1_DATE",

n.UDF2_VALUE AS "UDF2_VALUE",

v.VEND_NAME AS "VEND_NAME",

v.FIRST_NAME AS "VEND_FIRST_NAME",

v.LAST_NAME AS "VEND_LAST_NAME",

SUBSTR(d.DCS_CODE,1,3) AS "D_CODE",

SUBSTR(d.DCS_CODE,4,3) AS "C_CODE",

SUBSTR(d.DCS_CODE,7,3) AS "S_CODE",

d.D_NAME AS "D_NAME",

d.C_NAME AS "C_NAME",

d.S_NAME AS "S_NAME",

d.D_LONG_NAME AS "D_LONG_NAME",

d.C_LONG_NAME AS "C_LONG_NAME",

d.S_LONG_NAME AS "S_LONG_NAME",

d.ACTIVE AS "DCS_ACTIVE",

n.COST AS "ITEM_COST"

FROM INVN_SBS n

LEFT JOIN INVENTORY u ON n.ITEM_SID = u.ITEM_SID

LEFT JOIN VENDOR v ON v.VEND_CODE = n.VEND_CODE AND v.SBS_NO = n.SBS_NO

LEFT JOIN DCS d ON d.DCS_CODE = n.DCS_CODE AND d.SBS_NO = n.SBS_NO

WHERE n.SBS_NO in $(subsidiaries) AND NOT n.DESCRIPTION1 = 'LPG BUTANE';

i used the above and in my expression sum(invc_ext_price) then under dimension i mark "Suppress Value when Null" and it worked fine.