Evaluating a column with multiple values

[brf10043]

All,

I have an excel file that contains a column of Commodity Codes.  On another sheet I have a list of valid Commodity codes.  I need to create a table with a new column that indicates if the a commodity code in the Commodity Codes column is valid or not.  The Commodity Codes columns can have multiple Codes serrated by a columns (see screenshot below)  If any of the codes are valid the in the original column are valid, then the evaluation should indicate that the Commodity Code is valid.

Suggestions?

[trdandamudi]

One way is as below:

Mapping_CommodityCodes:
Mapping Load * Inline [
CommodityCodes,Valid
HCOCRS,Yes
HCPFOF,Yes
HCOBBK,Yes
HCOFFG,Yes
HCORFR,Yes
COLSLO,Yes
];

Data:

Final_Data:
Load CommodityCodes,
ApplyMap('Mapping_CommodityCodes', CommodityCodes,'No') As Valid_Yes_No ;

Load Subfield(CommodityCodes,',') as CommodityCodes
;
Load * Inline [
CommodityCodes
HCOCRS,HCPFOF,HCOBBK,HCOFFG,HCORFR,HCOSMS
COLSLO
](delimiter is '|');

[brf10043]

Thanks Thirumala, !

I'm a little new to all this so I could use a little more hand holding.  I have an excel file with 2 tabs.  The first tab include many columns including Request ID as the identifier and Q8.2 Commodity Codes Selected containing the Commodity codes with some rows having multiple.  I then have a second tab titled Valid Codes that contains a list of valid codes (Commodity Codes).  How do I apply the logic above using the spreadsheet?

[MarcoWedel]

Please post some sample data and your expected result.

thanks

regards

Marco

[trdandamudi · Accepted solution]

No issues... Please see the attached files where I created the excel file the way you want it. Please go over it and modify accordingly. Hope this helps:

Mapping_CommodityCodes:

Mapping LOAD *,

[Valid Codes] as CommodityCodes

FROM

(ooxml, embedded labels, table is Sheet2);

Final_Data:

Load CommodityCodes,

If(Isnull(Applymap('Mapping_CommodityCodes',CommodityCodes,null())),'N','Y') as [Valid Codes];

Load Subfield(CommodityCodes,',') as CommodityCodes

LOAD [Request ID],

    [Q8.2 Commodity Codes Selected] as CommodityCodes

FROM

(ooxml, embedded labels, table is Sheet1);

[brf10043]

Thanks for your help!

[trdandamudi]

You are welcome.