0

I am trying to rebuild some MS Access update query logic with R's merge function, as the update query logic is missing a few arguments.

Table link Google drive In my database "Invoice Account allocation", there are 2 tables:

Account_Mapping Table:

Origin  Origin Postal Destination   Destination      Invoice
country code          country       postal code      Account
FRA       01           GBR            *               ZR001
FRA       02           BEL            *               ZR003
BEL       50           ARG            *               ZR002
GER       01           ITA            *               ZR002
POL       02           ESP            *               ZR001
ESP       50           NED            *               ZR003
*         95           FRA           38               ZR001
BEL        *            *             *               ZR002
*          *            *             *               ZR003
FRA        *          FRA            25               ZR004

Load_ID

 ID            Origin   Postal  Destination  Destination    Default 
               country   code                postal code    Invoice Account
2019SN0201948   FRA       98    FRA             38           XXAC001
2019SN0201958   POL       56    GBR             15           XXAC001
2019SN0201974   BEL       50    ARG             27           XXAC001
2019SN0201986   FRA       02    GER             01           XXAC001

The default invoice account in tables (Load_ID and Status_ID) is updated by the invoice account from the Account_Mapping table.

The tables Account_Mapping and Load_ID can be joined by: Origin country & Origin country, Origin Postal code & Postal code, Destination country & Destination, and Destination postal code & Destination postal code.

In the account_mapping table, there are several "*", it means the string value can take any value. I am not able build this logic with merge function. Please help me with a better logic. 

New_Assigned_Account_Final <- merge(Load_ID, Account_Mapping, by.x = 
c("Origin country","Postal code","Destination", "Destination postal code"), 
by.y = 
c("Origin country","Origin Postal code","Destination country", "Destination 
postal code"))

Desired result:

Updated Load_ID table as below.

Load_ID: 

    ID        Origin    Postal  Destination  Destination    Default 
               country   code                postal code    Invoice Account
2019SN0201948   FRA       98    FRA             38             ZR003
2019SN0201958   POL       56    GBR             15             ZR003
2019SN0201974   BEL       50    ARG             27             ZR002
2019SN0201986   FRA       02    GER             01             ZR003

For the first ID, the default ID becomes "ZR003" because, "FRA" as Origin country doesn't have a Postal code - "98", so it falls under the all "*" bucket and is allocated to ZR003.

For the third ID, the default ID becomes "ZR002" because, "BEL" as Origin country has a Postal code - "50" associated with it, and the destination postal code of "ARG" can be anything because of the "*" in the Destination postal code column, therefore it is allocated to ZR002.

Thank you for your inputs.

vinod vijay
  • 1
  • 1
  • 1
    Please do not post an image of code/data/errors: it cannot be copied or searched (SEO), it breaks screen-readers, and it may not fit well on some mobile devices. Ref: https://meta.stackoverflow.com/a/285557/3358272. One goal you might take upon yourself is to make it as easy as possible for people to "play" with your data to come up with a working example; asking us to transcribe from an image might happen but is less likely to draw the attention you want. – r2evans Feb 18 '19 at 00:30
  • Thanks for your input. Now I have added a google drive link. I should have thought about this earlier. – vinod vijay Feb 18 '19 at 05:18
  • 1
    There are two reasons why linked files like that may not be popular: (1) when the link goes stale (e.g., you revoke public access), the question becomes unreproducible; and (2) I'm not fond of clicking on random links. Granted, I tend to *believe/hope* that links on google-drive would be virus-checked, I don't *know* that is true. There are a few good references that discuss different aspects of a self-contained reproducible question: https://stackoverflow.com/questions/5963269, https://stackoverflow.com/help/mcve, and https://stackoverflow.com/tags/r/info. – r2evans Feb 18 '19 at 05:42
  • I have added the tables in the description itself. Kindly take a look. – vinod vijay Feb 18 '19 at 20:55
  • 1
    Please post desired results from your small data sample. Not clear how asterisks are handled. – Parfait Feb 18 '19 at 21:49
  • Hi, I have just added the desired result, please have a look and give your suggestion. – vinod vijay Feb 18 '19 at 22:37

0 Answers0