Apply Map

[#1]

Hi Experts,

How to use Apply Map? Can anyone share sample app

Please find attached sample data

Sheet1- Product Name

Sheet2 - Product Name to Map with Name

Thanks

[Digvijay_Singh]

Load Sheet 2 table;

Create new mapping table as -

Name_Map:

Mapping load

     Product Name,

     Name

Resident <Name of your Sheet2 Table Here>;

Then use this as below while loading Sheet1;

Load

     Product ID,

     Product Name,

     Applymap('Name_Map',Product Name,'Unknown') as Name,

     Start Date,

     End Date

From <Sheet1>

You may change sequence in different ways but the whole idea is to load mapping table first using the already loaded table and use the info in subsequent loading as I used above. If No name corresponding to Product Name then 'Unknown' value will be loaded.

[settu_periasamy]

Hi,

Directory;
MAP:
Mapping LOAD [Product name],
Name
FROM
[Apply Map.xlsx]
(ooxml, embedded labels, table is Sheet2);


Final:
LOAD [Product ID],
[Product name],
Date#([Start Date],'M/D/YYYY') as  [Start Date],
Date#([End Date],'M/D/YYYY') as  [End Date],
ApplyMap('MAP',[Product name],'Unknown') as Name
FROM
[Apply Map.xlsx]
(ooxml, embedded labels, table is Sheet1);

[tamilarasu]

Hi,

Try like below,

ProductMapping:
Mapping LOAD [Product name], 
     Name
FROM
[Apply Map.xlsx]
(ooxml, embedded labels, table is Sheet2);

Product:
LOAD [Product ID], 
  [Product name],
     Applymap('ProductMapping',[Product name], null()) as FullName,
     [Start Date], 
     [End Date]
FROM
[Apply Map.xlsx]
(ooxml, embedded labels, table is Sheet1);

[jagan · Accepted solution]

Hi,

Try like this

ProductsMapping:

MAPPING LOAD [Product name],

     Name

FROM

[Apply Map.xlsx]

(ooxml, embedded labels, table is Sheet2);

Data:

LOAD [Product ID],

     [Product name] AS ActualProductName,

     ApplyMap('ProductsMapping', [Product name], 'NA') AS [Product name],

     [Start Date],

     [End Date]

FROM

[Apply Map.xlsx]

(ooxml, embedded labels, table is Sheet1);

Regards,

Jagan.

[jagan]

Hi,

You can also try join

Data:

LOAD [Product ID],

     [Product name] AS ActualProductName,

    [Product name],

     [Start Date],

     [End Date]

FROM

[Apply Map.xlsx]

(ooxml, embedded labels, table is Sheet1);

LEFT JOIN(Data)

LOAD [Product name],

     Name

FROM

[Apply Map.xlsx]

(ooxml, embedded labels, table is Sheet2);

[#7]

Hi,

PFB

Product:

Mapping

LOAD [Product name],

     Name

FROM

(ooxml, embedded labels, table is Sheet2);

Details:

LOAD [Product ID],

     [Product name],

     [Start Date],

     [End Date],

     ApplyMap('Product',[Product name],'NA') as ProdcutDesc

FROM

(ooxml, embedded labels, table is Sheet1);

Regards,

Sub2u444