Exclude values in script

[Anonymous]

Hi

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

[stevedark]

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

[#3]

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!

[Anonymous]

Use preceding load

load purgechar(Suppliers,'[') ;

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

              or      subfield(suppliers,'_',1)