Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
@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.
@raadwiptec on same payment date there would be many players in source file, so how would map the player id from mapping file?
@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.
@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.