none
Dynamically assignment for time booking codes to resources RRS feed

  • Question

  • Hi All,

    I am looking for your expert suggestion if we can dynamically assign network codes to multiple resources using power query or not ?

    for example :-

    Table 1 contains data related to resources name and , how much hours they can book in multiple network code.

    Name Signum Productive Hrs.
    A EASDWE 34
    B ERTYUGF 42.5
    C EASQWET 42.5
    D QWESDTR 0
    E ERTYERD 42.5
    F QWETFSF 42.5
    G JHYTRT 38
    H JKUYTBN 42.5

    Table 2 contains below data which contains network codes and maximum hrs resources can book on that code.

    ICRRB Hours
    12386755 147.25
    17643021 12
    17643021 66.25
    10745210 20
    10345606 40
    10775345 15

    Now I want to create a final output where these network code should map to multiple resources and also needs to taken care of table 1 productive columns for how much a hrs. a resource can book(Maximun 1 resource can book 42.5 and minimum 0).

    Also we have to at least assign 2 network codes and maximum 4 to the resources. 

    For better understanding I have uploaded a sample file in given link.

    Also sample output pasted below:-

    ICRRB A B C D E F G H Remaining
    12386755 5 19.5 30   30 12.5 30 20.25 0
    17643021 4 8             0
    17643021 10 10 12.5   12.5 20 1 0.25 0
    10745210 15 5             0
    10345606           1.25 7 22 9.75
    10775345           8.75     6.25
    Total Productive 34 42.5 42.5 0 42.5 42.5 38 42.5


     


    AskQuery1984

    Thursday, June 4, 2020 9:53 AM

All replies

  • Hi there. It isn't clear to me how you get from the two input tables to the sample output. How are the two input tables related?

    Ehren

    Tuesday, June 9, 2020 11:31 PM
    Owner
  • Hi Ehren,

    Both the tables are different and right now my manually task is to assign Signum/Name to any of ICRRB code.

    its like many to many relationship by by creating any common column with same values. 

    And above Output table is just a sample. there are many possible combination for output. 

    we just need to care only that hrs. allotted in final output should not be exceed Productive hrs for respective Name/Signum.

    and at least 2 ICRRB codes needs to assign to 1 Signum/Name.

    Name Signum Productive Hrs.
    A EASDWE 34
    B ERTYUGF 42.5
    C EASQWET 42.5
    D QWESDTR 0
    E ERTYERD 42.5
    F QWETFSF 42.5
    G JHYTRT 38
    H JKUYTBN 42.5

    ICRRB Hours
    12386755 147.25
    17643021 12
    17643021 66.25
    10745210 20
    10345606 40
    10775345 15

    AskQuery1984


    • Edited by AskQuery1984 Wednesday, June 10, 2020 10:00 AM Headers were not visible, so changed the color of headers
    Wednesday, June 10, 2020 9:58 AM
  • Hm. It's still not clear to me how they relate. It sounds like there may be a manually created relationship table you didn't include above?

    Ehren

    Wednesday, June 10, 2020 10:13 PM
    Owner
  • Hi Ehren,

    There is no existing relationship between both tables. but in case we have to create relationship then we can assume like below table for many to many relationship. I have seen in one of online search but later on not sure how to create custom functions.

    Name Signum Productive Hrs. common
    A EASDWE 34 1
    B ERTYUGF 42.5 1
    C EASQWET 42.5 1
    D QWESDTR 0 1
    E ERTYERD 42.5 1
    F QWETFSF 42.5 1
    G JHYTRT 38 1
    H JKUYTBN 42.5 1

    ICRRB Hours common
    12386755 147.25 1
    17643021 12 1
    17643021 66.25 1
    10745210 20 1
    10345606 40 1
    10775345 15 1



    AskQuery1984

    Friday, June 12, 2020 5:49 AM
  • What I suspect is that the OP wants to assign
    multiple account hours to multiple jobs hours.
    Hours may be split to some extend (2 to 4)
    among accounts and jobs.
    Here is a fictitious solution where the total account hours
    are the same as the total job hours.
    Since this is a knapsack problem, oodles of solutions may exist.
    http://www.mediafire.com/file/28en3wtduy1l4vq/06_13_20a.xlsx/file

    Sunday, June 14, 2020 4:19 AM
  • Hi Seidenberg,

    Thank you for your time. I have checked the file but not able to find any query only queries. only formulas in few cells. but got some idea. will check if can any array or dynamic formula can work .... ?



    AskQuery1984

    Monday, June 15, 2020 1:44 PM