Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
brf10043
Contributor

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?

Tags (2)
1 Solution

Accepted Solutions
trdandamudi
Honored Contributor II

Re: Evaluating a column with multiple values

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
Honored Contributor II

Re: Evaluating a column with multiple values

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
Contributor

Re: Evaluating a column with multiple values

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?

Re: Evaluating a column with multiple values

Please post some sample data and your expected result.

thanks

regards

Marco

trdandamudi
Honored Contributor II

Re: Evaluating a column with multiple values

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

brf10043
Contributor

Re: Evaluating a column with multiple values

Thanks for your help!

trdandamudi
Honored Contributor II

Re: Evaluating a column with multiple values

You are welcome.