topic Re: How do I use a subfield as an input to a ApplyMap? in QlikView

How do I use a subfield as an input to a ApplyMap?

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

I have a table with Products, Parent Products, and Prices:

Product	Parent _Products	Price
Product A		1
Product B	Product A	2
Product C	Product A, Product B	3

I would like to add a column "Parent" where each parent product is its own row, and I would like to add a column "Parent Price" that includes the price of the value in "Parent.

Product	Parent_Products	Price	Parent	Parent_Price
Product A		1
Product B	Product A	2	Product A	1
Product C	Product A, Product B	3	Product A	1
Product C	Product A, Product B	3	Product B	2

I've tried the following code:

Prod_Price_Map:
Mapping Load Product, Price

from [filepath](qvd);

Price_Table:

Load Product,

Parent_Products,

Price,

subfield(Parent_Products,', ') as Parent,

ApplyMap('Prod_Price_Map', PARENT) as PARENT_PRICE

From [filepath](qvd);

I get an error when I try to use the Parent field I created as an input to the Apply Map. error below:

Field not found - <Parent>

If I comment that line out everything else works as intended.

Re: How do I use a subfield as an input to a ApplyMap?

Anil_Babu_Samineni — Sat, 14 Apr 2018 05:19:38 GMT

James Heaton wrote:

Prod_Price_Map:

Mapping Load Product, Price

from [filepath](qvd);

ApplyMap('Prod_Price_Map', PARENT) as PARENT_PRICE

Field not found - <Parent>

Because, Parent Field doesn't show in Mapping Table "Prod_Price_Map ?? Perhaps this?

Prod_Price_Map:
Load Product, Price

from [filepath](qvd);

Price_Table:

Mapping Load Product,

Parent_Products,

Price,

subfield(Parent_Products,', ') as Parent,

From [filepath](qvd);

Final:

NoConcatenate

Load Product,, Parent_Products, Price, Parent, Price, ApplyMap('Prod_Price_Map', Parent) as PARENT_PRICE Resident Price_Table;

Re: How do I use a subfield as an input to a ApplyMap?

MarcoWedel — Sat, 14 Apr 2018 23:10:55 GMT

maybe helpful:

Bill of Materials

regards

Marco

Re: How do I use a subfield as an input to a ApplyMap?

tamilarasu — Mon, 16 Apr 2018 02:05:34 GMT

Hi James,

Script:

Price_Table:
LOAD Product,
     [Parent _Products],
     Price,
     Subfield([Parent _Products],', ') as Parent
FROM
[https://community.qlik.com/thread/298229]
(html, codepage is 1252, embedded labels, table is @1);

Left Join (Price_Table)
LOAD Distinct Product as Parent,
     Price as Parent_Price
Resident Price_Table;

Prod_Price_Map:
Mapping LOAD Product, 
     Price
FROM
[https://community.qlik.com/thread/298229]
(html, codepage is 1252, embedded labels, table is @1);

Price_Table:
Load *, Applymap('Prod_Price_Map',Parent) as Parent_Price;
LOAD Product,
     [Parent _Products],
     Price,
     SubField([Parent _Products],', ') as Parent
FROM
[https://community.qlik.com/thread/298229]
(html, codepage is 1252, embedded labels, table is @1);

Result: