Mapping Files monthly

[raadwiptec]

My Source File Fields

PaymentDate | Player | Currency | Amount

05/01/2021 | XAR |EUR | 9345

20/02/2021 | TKR |EUR | 113

We Would be recieving every month new mapping Files

Jan_PlayCode.xlsx

Feb_PlayCode.xslx

Mar_PlayCode.xslx etc..

The Mapping File has the following fields

Effective Date | PlayerCode 

08/01/2021| XAR324

22/03/2021| FRA654

if the paymentdate are with jan 2021 then it should look onto Jan mapping files and give the playercode 

if the paymentdate are with feb 2021 then it should look onto Feb mapping files and give the playercode 

[Kushal_Chawda]

@raadwiptec  on same payment date there would be many players in source file, so how would map the player id from mapping file?

[raadwiptec]

@kushal - The Player and the currency are unique in the source file. 

Also in the mapping i forgot to add. 

Effective Date | PlayerCode |Player |Currency

08/01/2021| XAR324 |XAR|EUR

22/03/2021| FRA654|FRA|EUR

the idea is to link the Player and the currency between 2 Files but while checking the PaymentDate ..the player code should be taken from the Corresponding Files.. Beacuse the Player code is having a tendency to change over time.

[Kushal_Chawda · Accepted solution]

@raadwiptec  try below

Map_player_code:
mapping LOAD monthstart(floor([Effective Date])) & Player & Curency as Key,
                              [Player Code]
FROM
[C:\*PlayCode.xlsx]
(ooxml, embedded labels, table is Sheet1);

Table:
LOAD [Payment Date],
           applymap('Map_player_code', monthstart(floor([Payment Date])) & Player & Curency,'NA') as                  Player_Code
         Player,
        Curreny,
        Amount
FROM table;

Note:  If you want to compare Payment Date & Effective Date as is then remove monthstart function while creating key in mapping table and applymap.