Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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;
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;
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;
Or
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: