topic Re: Evaluating a column with multiple values in QlikView

Evaluating a column with multiple values

brf10043 — Wed, 25 Nov 2020 16:16:04 GMT

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?

Re: Evaluating a column with multiple values

trdandamudi — Mon, 04 Dec 2017 21:31:25 GMT

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

Re: Evaluating a column with multiple values

brf10043 — Mon, 04 Dec 2017 22:56:39 GMT

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

MarcoWedel — Tue, 05 Dec 2017 00:09:59 GMT

Please post some sample data and your expected result.

thanks

regards

Marco

Re: Evaluating a column with multiple values

trdandamudi — Tue, 05 Dec 2017 00:11:09 GMT

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

Re: Evaluating a column with multiple values

brf10043 — Tue, 05 Dec 2017 00:45:35 GMT

Thanks for your help!

Re: Evaluating a column with multiple values

trdandamudi — Tue, 05 Dec 2017 01:11:57 GMT

You are welcome.