topic Re: Excluding fields from a table in QlikView

Excluding fields from a table

Thu, 12 Sep 2013 13:30:21 GMT

I want to exclude certain values in a table.

For eg , In the Category table below , I want to excluded PRICE , LOA & REPORT GROUP . How do I do the scripting ?

Re: Excluding fields from a table

Anonymous — Thu, 12 Sep 2013 13:31:52 GMT

Nayan,

If you want to do this on the Script, you could use the inner join:

//Example

// To keep only Category

// TV & VIDEO

// TORCHES

// Use

inner Join ( FullTab )
SelectedValues:
LOAD * INLINE [
Category
TV & VIDEO
TORCHES
]

Hope I Could Help

Re: Excluding fields from a table

MayilVahanan — Thu, 12 Sep 2013 13:33:14 GMT

Try like this

Load If(not wildmatch(Category, 'PRICE' , 'LOA','REPORT GROUP'), Category) as Category from tablename;

Hope that helps

Re: Excluding fields from a table

Thu, 12 Sep 2013 13:55:02 GMT

thanks. Will try and let you know.

Re: Excluding fields from a table

Carlos_Reyes — Thu, 12 Sep 2013 14:18:48 GMT

It's just the same as Mayil suggested but in the case you want to load more fields than Category from the same table, you should move that condition to the WHERE clause:

LOAD

Category,

Field2,

Field3

FROM TableName

WHERE NOT MATCH (Category, 'PRICE' , 'LOA','REPORT GROUP')

;

Re: Excluding fields from a table

Thu, 12 Sep 2013 14:24:32 GMT

Hi,

PFA

Re: Excluding fields from a table

Anonymous — Thu, 12 Sep 2013 14:27:18 GMT

Hi,

You can do that like this:

LOAD

Category
FROM

XXX

Where

Category<>'PRICE'

AND

Category<>'LOA'

AND

Category<>'REPORT GROUP'

;

Re: Excluding fields from a table

Thu, 12 Sep 2013 14:32:28 GMT

Hi All

Thanks for all the responses. The actual model i have a a bit more complicated. I'll try the formulae given and give feedback.

kind regards

Nayan

Re: Re: Excluding fields from a table

Fri, 13 Sep 2013 09:40:38 GMT

The formula dont seem to work. What I have done is created an extract of my database in excel.

Below are 5 items with main category and sub category (I've also attached an excel file)

Also attached is a list of main Categories and Sub-Categories.

In the table below i dont want to lines if the Main Category Code = PRICE, REPORTGRP or LOA . How do i do the scripting. For each item there should be only one line.

Just to let you know, on our database for Main Category Code equal to:

PRICE - the Sub Category Code is a any value (see excel file attached)
REPORTGRP - the Sub Category code B/O, WPU, wpu , WPY and also blank
LOA - the Sub Category code is blank.

Your help would be appreciated.

kind regards

Nayan

Re: Excluding fields from a table

Fri, 13 Sep 2013 09:42:34 GMT

The formula dont seem to work. What i have done is create an extract of my database in excel.

Below are 5 items with main category and sub category (I've also attached an excel file)

Also attached is a list of main Categories and Sub-Categories.

In the table below i dont want to lines if the Main Category Code = PRICE, REPORTGRP or LOA . For each item there should be only one line.

Just to let you know, on our database for Main Category Code equal to:

PRICE - the Sub Category Code is a any value (see excel file attached)
REPORTGRP - the Sub Category code B/O, WPU, wpu , WPY and also blank
LOA - the Sub Category code is blank.

Your help would be appreciated.

kind regards

Nayan

Re: Re: Excluding fields from a table

Fri, 13 Sep 2013 09:45:08 GMT

The formula dont seem to work. What I have done is created an extract of my database in excel.

Below are 5 items with main category and sub category (I've also attached an excel file)

Also attached is a list of main Categories and Sub-Categories.

In the table below i dont want to lines if the Main Category Code = PRICE, REPORTGRP or LOA . How do i do the scripting. For each item there should be only one line.

Just to let you know, on our database for Main Category Code equal to:

PRICE - the Sub Category Code is a any value (see excel file attached)
REPORTGRP - the Sub Category code B/O, WPU, wpu , WPY and also blank
LOA - the Sub Category code is blank.

Your help would be appreciated.

kind regards

Nayan

Re: Re: Excluding fields from a table

Anonymous — Fri, 13 Sep 2013 09:56:28 GMT

Hi,
You can do that like this:

LOAD
// All your other fields,
[Main Category Code]
FROM
// your file
Where
[Main Category Code]<>'PRICE'
AND
[Main Category Code]<>'LOA'
AND
[Main Category Code]<>'REPORTGRP'
;

Re: Excluding fields from a table

Fri, 13 Sep 2013 10:21:16 GMT

Hi Bobbyraj

Below is the actual scripting for the model im using. Where do I insert your scripting.

Regards

Nayan

ICITEMO:

LOAD

ITEMNO as ,

OPTFIELD as ,

VALUE as ;

SQL SELECT *

from ICITEMO;

OptfldDetail:

LOAD OPTFIELD as ,

FDESC as ;

SQL SELECT *

FROM CSOPTFH;

left join(OptfldDetail)

LOAD OPTFIELD as ,

VALUE as ,

// AUDTDATE,

// AUDTTIME,

// AUDTUSER,

// AUDTORG,

// SORTEDVAL,

VDESC as ;

// TYPE,

// LENGTH,

// DECIMALS,

// ALLOWNULL,

// VALIDATE;

SQL SELECT *

FROM CSOPTFD;

left join (ICITEMO)

Load * resident OptfldDetail;

drop table OptfldDetail;

Re: Excluding fields from a table

Anonymous — Fri, 13 Sep 2013 12:00:49 GMT

Hi Nayan,

Sorry but I don't understand your script...

You don't give any name after your 'as' so your script can't work!

Otherwise for your question, you have to insert the condition:

Where

Category<>'PRICE'

AND

Category<>'LOA'

AND

Category<>'REPORT GROUP'

;

After each table where you are getting the field Category from.

I'm afraid you can't insert the condition Where after SQL SELECT, you have to check that.

But in that case, you just have to load everything in a temporary table with SQL SELECT, then to load a new table with the condition from your temporary table then drop it.

If you can send a copy of your qvw it will be easyer to help you

Re: Excluding fields from a table

Fri, 13 Sep 2013 12:12:52 GMT

Hi Bobbyraj

The QV Model that I am working with is 300MB and reading from our server.

I’ll find a way to put the extract of the database in excel and design a simple QV model around that so you can work with.

Kind regards

Nayan