topic Re: Exclude values in script in QlikView

Exclude values in script

Anonymous — Tue, 05 May 2015 06:56:31 GMT

I have a list of suppliers delimited by underscores. [Alcatel_Apple_LG_Nokia_Sony_Rim]]

I also have a list of suppliers to exclude by country, delimited by comma.

[FRA001,Other1,Nokia,Rim

BEL001,Other2,LG,Rim]

I would like to retrieve a the list of suppliers to include per country in form of a table.

Expected result:

FRA001,Other1, Alcatel

FRA001,Other1, Apple

FRA001,Other1, LG

FRA001,Other1, Sony

BEL001,Other2, Alcatel

BEL001,Other2, Apple

BEL001,Other2, Nokia

BEL001,Other2, Sony

I would like to know if there is a way to build such a table to exclude values in the script?

Thanks

Hasnaa

Re: Exclude values in script

stevedark — Tue, 05 May 2015 07:09:09 GMT

I'm not sure I follow what your source data looks like here, but there are a few techniques you should look at.

You can split a string into rows on a delimiter using SubFIeld, like this:

load

subfield('Alcatel_Apple_LG_Nokia_Sony_Rim', '_') as Manufacturer

autogenerate(1)

;

Your exclude list could be more problematic to parse, as you appear to have commas used as the delimiters between the first two fields, but also to split the manufacturer? You will need to deal with these as a cross table,

CROSSTABLE (Ignore,Manufacturer,2) LOAD

INLINE

[

Code,Type,Man1,Man2,Man3,Man4,Man5,Man6

FRA001,Other1,Nokia,Rim

BEL001,Other2,LG,Rim]

;

Once you have your separate data sets you can then use functions like WHERE EXISTS and LEFT KEEP in order to include or exclude values.

Without having a better handle on your source data and requirement though I can't suggest further.

Steve

Re: Exclude values in script

Tue, 05 May 2015 07:09:14 GMT

Hi Hasnaa,

You can use the subfield function in the script to split the different values apart but you still need to have a logical connection/link between the data fields to be able to create such a table you are showing.

Good luck!

Re: Exclude values in script

Anonymous — Tue, 05 May 2015 07:18:07 GMT

Use preceding load

load purgechar(Suppliers,'[') ;

load left( suppliers, index( suppliers,'_') -1 ) as suppliers

or subfield(suppliers,'_',1)