Splitting a data column into 2 columns during import

[Anonymous]

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!

[mdmukramali]

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;

[pradosh_thakur]

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;