Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi there!
I would like to ask how I can approach the following:
I have a dataset containing the following:
Product Information
ProductID | Price| Quantity | Expiry
A001 | $50 | 10 | Dec-19
A002 | $40 | 15 | Dec-19
A003 | $30 | 15 | Dec-18
B005 | $35 | 20 | Dec-18
B006 | $45 | 20 | Dec-19
Those productID starting with 'A' are old productIDs and 'B' are new productIDs.
I have another dataset that contains the information to link the old and new ProductIDs.
ProductID dataset
ProductIDold | ProductIDnew
A001 | B001
A002 | B002
A003 | B003
ProductIDs that cannot be found in the table will be taken as is.
When importing into Qliksense, I would like to seperate the new and the old productIDs to form the following table.
ProductIDold |ProductIDnew | Price| Quantity | Expiry
A001 | B001 | $50 | 10 | Dec-19
A002 | B002 | $40 | 15 | Dec-19
A003 | B003 | $30 | 15 | Dec-18
- | B005 | $35 | 20 | Dec-18
- | B006 | $45 | 20 | Dec-19
So to summarize what needs to be performed:
1) Seperate the ProductID column to ProductIDold and ProductIDnew columns when importing. The other 3 columns will be imported accordingly(Price,Quantity,Expiry).
2) Add the ProductIDnew if there is a ProductIDold using the ProductID dataset
I've tried the following for part 1) but didn't seem to work.
load
if(wildmatch(productID,'A'),productID) as ProductIDold,
if(wildmatch(productID,'B'),productID) as ProductIDnew,
Price,
Quantity,
Expiry
Thanks in advance!
Hi,
Can you try using ApplyMap:
Product_Map:
Mapping LOAD * Inline
[
ProductIDold,ProductIDnew
A001,B001
A002,B002
A003,B003
]
;
Product_Information:
LOAD *,
if(WildMatch(ProductID,'A*'),ProductID) as ProductIDold,
ApplyMap('Product_Map',ProductID) as ProductIDnew
Inline[
ProductID,Price, Quantity,Expiry
A001,$50 ,10 ,Dec-19
A002,$40 ,15 ,Dec-19
A003,$30 ,15 ,Dec-18
B005,$35 ,20 ,Dec-18
B006,$45 ,20 ,Dec-19
]
;
DROP Field ProductID;
map1:
mapping
load ProductIDold as ProductID,ProductIDnew;
load * inline [
ProductIDold | ProductIDnew
A001 | B001
A002 | B002
A003 | B003
] (delimiter is |);
load *,ApplyMap('map1',ProductID,ProductID) as ProductIDnew,PICK(WildMatch(ProductID,'A*'),ProductID) AS ProductIDold;
load * inline [
ProductID | Price| Quantity | Expiry
A001 | $50 | 10 | Dec-19
A002 | $40 | 15 | Dec-19
A003 | $30 | 15 | Dec-18
B005 | $35 | 20 | Dec-18
B006 | $45 | 20 | Dec-19
] (delimiter is |);
DROP FIELD ProductID;