Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
brf10043
Creator
Creator

Evaluating a column with multiple values

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?

1 Solution

Accepted Solutions
trdandamudi
Master II
Master II

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

View solution in original post

6 Replies
trdandamudi
Master II
Master II

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
Creator
Creator
Author

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
Master II
Master II

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
Creator
Creator
Author

Thanks for your help!

trdandamudi
Master II
Master II

You are welcome.