Page 1 of 2 1 2 LastLast
Results 1 to 10 of 14

Thread: Record related to other record

  1. #1
    Seeker a.alfano's Avatar
    Join Date
    Oct 2019
    Posts
    5
    Articles
    0
    Excel Version
    2016

    Record related to other record



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi everyone
    I have to estimate the product sales through the sales of other product.
    In other words I have a simple table like this:
    PRODUCT QUANTIY
    A 10
    B 20

    I have another table that indicate the rules to estimate the quantity of other product.
    In other words I have a simple table like this:
    PRODUCT PRODUCT RELATED RULES
    A AA +3
    B BB +1
    AA AAA +1
    BB BBB +3

    I'd like to produce a table like this

    PRODUCT RELATED QUANTIY
    AA 13
    BB 21
    AAA 14
    BBB 24

    Thank you very much in advance

  2. #2
    Acolyte horseyride's Avatar
    Join Date
    Nov 2017
    Posts
    94
    Articles
    0
    Excel Version
    Office 365 Pro Plus 1708.8431.
    I have no idea where the bottom two rows of your output table come from, but otherwise I'd suggest (a) in table 1, do a merge query with table2 use product as the matching key by clicking that column on top and bottom, join type left outer (2) expand merge by clicking arrows atop column (3) add column .. custom column ... that sums original Quantity and merged Rules Column

    Code:
    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        #"Merged Queries" = Table.NestedJoin(Source,{"PRODUCT"},Table2,{"PRODUCT"},"Table2",JoinKind.LeftOuter),
        #"Expanded Table2" = Table.ExpandTableColumn(#"Merged Queries", "Table2", {"RULES"}, {"Table2.RULES"}),
        #"Added Custom" = Table.AddColumn(#"Expanded Table2", "Revised Quantity", each [QUANTIY]+[Table2.RULES])
    in #"Added Custom"

  3. #3
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,682
    Articles
    0
    Excel Version
    O365
    Quote Originally Posted by horseyride View Post
    I have no idea where the bottom two rows of your output table come from, ...
    AAA = AAA from AA (+1), AA from A (+3), A is 10, =14.

    BBB - similar.

    That's the difficulty.
    Last edited by Bob Phillips; 2019-10-23 at 09:19 PM.

  4. #4
    Acolyte horseyride's Avatar
    Join Date
    Nov 2017
    Posts
    94
    Articles
    0
    Excel Version
    Office 365 Pro Plus 1708.8431.
    Ah. Load table1. Then for table2
    t1t2.xlsx

    Code:
    let
        Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
        #"Merged Queries" = Table.NestedJoin(Source,{"PRODUCT"},Table1,{"PRODUCT"},"Table1",JoinKind.LeftOuter),
        #"Expanded Table1" = Table.ExpandTableColumn(#"Merged Queries", "Table1", {"QUANTIY"}, {"Table1.QUANTIY"}),
        #"Added Custom" = Table.AddColumn(#"Expanded Table1", "Sum", each [RULES]+[Table1.QUANTIY]),
        #"Merged Queries1" = Table.NestedJoin(#"Added Custom",{"PRODUCT"},#"Added Custom",{"PRODUCT RELATED"},"Added Custom",JoinKind.LeftOuter),
        #"Expanded Added Custom" = Table.ExpandTableColumn(#"Merged Queries1", "Added Custom", {"Sum"}, {"Sum.1"}),
        #"Replaced Value" = Table.ReplaceValue(#"Expanded Added Custom",null,0,Replacer.ReplaceValue,{"Table1.QUANTIY", "Sum", "Sum.1"}),
        #"Added Custom1" = Table.AddColumn(#"Replaced Value", "Quantity", each [RULES]+[Table1.QUANTIY]+[Sum.1]),
        #"Removed Other Columns" = Table.SelectColumns(#"Added Custom1",{"PRODUCT RELATED", "Quantity"})
    in
        #"Removed Other Columns"

  5. #5
    Seeker a.alfano's Avatar
    Join Date
    Oct 2019
    Posts
    5
    Articles
    0
    Excel Version
    2016
    Yes, this is exactly what I'm looking for, thank you very very much.

  6. #6
    Seeker a.alfano's Avatar
    Join Date
    Oct 2019
    Posts
    5
    Articles
    0
    Excel Version
    2016
    Hi Neophyte,
    unfortunatelly the files seem doest't work if you add other raw in the rules table.
    If for example you add the line AAA / AAAA +10 in the table you will not get the 24 in the table result.
    Any cues?

  7. #7
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,286
    Articles
    0
    Excel Version
    Office 365 Subscription
    You are the forum neophyte (newbie)! Which of the forum members helping you did you mean to address?
    Ali
    Enthusiastic self-taught user of MS Excel!

  8. #8
    Seeker a.alfano's Avatar
    Join Date
    Oct 2019
    Posts
    5
    Articles
    0
    Excel Version
    2016

    Reply to Horseyride

    Yes, I m a neophyte and also my english is quite basic.
    My intention was to reply to Horseyride but I wrote the wrong name.
    The file attached from Hoseryide was good but unfortunatelly works only if I have 2 step in the table rule. Unfortunately I have a lot of step.
    Sorry and excuse me for my mistake.

  9. #9
    Acolyte horseyride's Avatar
    Join Date
    Nov 2017
    Posts
    94
    Articles
    0
    Excel Version
    Office 365 Pro Plus 1708.8431.
    Provide a more extensive example of the two tables and desired results, in table and not sentence format. Thanks

  10. #10
    Acolyte cyborgski's Avatar
    Join Date
    May 2019
    Posts
    60
    Articles
    0
    Excel Version
    Office 365
    I think what a.alfano is trying to describe is a repeated lookup and consolidation of the table until the parts no longer have lookups. This problem is hard to describe in a forum setting even with good English. PowerQuery solutions would involve List.Generate or a Recursive function.

    a.alfano, read the whole link below and see how similar it is to your process. If you can see how it applies then modify the code from the workbook in post #7. Hint, the recur function would look similar to horseyride's post #2 from this thread.
    https://www.excelguru.ca/forums/show...umns-in-the-db

    Regards,
    Mike

Page 1 of 2 1 2 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •