Excluding fields from a table

[#1]

Hi

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 ?

Category

TV & VIDEO

TORCHES

AUDIO OBSOLETE

LOA

APPLIANCES

BATTERIES

BRACKETS

CABLES

CABLE MANAGEMENT

CCTV

CLEANING KITS

CABLE SOLUTIONS OBSOLETE

COMPUTER NETWORKING

DISPLAY STANDS

ELLIES OBSOLETE

ELECTRICAL

ENERGY MONITORING

ELECTRICAL OBSOLETE

GENERATORS

PRICE

HDMI

HEADPHONES

REPORT GROUP

HOME SECURITY

INDUSTRIAL AUDIO

INVERTERS

LIGHTING

MULTIMEDIA

NETWORKING OBSOLETE

OUTDOOR OBSOLETE

PACKAGING

PORTABLE DEVICES

RAW MATERIALS ENGINEERING

REMOTE WORLD

SANWARE

SATELLITE

SHELVING

SIGNAL DISTRIBUTION

SATTELITE OBSOLETE

SOLAR

TELEPHONE

WIND POWER

[#2]

Hi

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

[Anonymous]

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

[#4]

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;

[Anonymous]

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

[#6]

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