Hi,
I'm trying to link multiple tables and can't seem to get it.
What I'm trying to do is return the item rate from the Pricing Schedule (using effective dates and work dates) and unit of measure (UM) for a particular item (FA_ID). I have an NL row formula to return all the instances where the item has been used (from the EQ_Usage Table) and need to return the Reg_Rate from the EQ_Rate Table. The EQ_Usage Table has the FA_ID and UM but the EQ_Rate Table only has the UM and a Seq_Num. Another table, EQ_Schd Table, has the FA_ID and Seq-Num, the kicker is there are multiple Seq_Num's for each FA_ID based on both the UM and Effective date.
My formula is
=NL("FIRST","EQ_RATE","REG_RATE","JOB_ID",C2,"UM",K5,"LINK=","EQ_SCHD","JOB_ID","=JOB_ID","SEQ_NUM","=SEQ_NUM","LINK=","EQ_USAGE","FA_ID","=FA_ID")
Anyone able to help me?
I have three tables with the following data;
Table 1 - EQ_ Rate
Field - Reg_Rate (value I want to return)
Field - Seq_Num
Field - Eff-Dat
Field - UM
Field - Job_ID
Table 2 - EQ_Schd
Field - FA_ID
Field - Seq_Num
Field - Eff_Date
Field - Job_ID
Table 3 - EQ_Usage
Field - FA_ID
Field - UM
Field - Date Used
Field - Job_ID
Date
Votes
2 comments
-
Jet Reports Historic Posts Hi,
It sounds like from what you're saying that there would be multiple values for Reg_Rate for each FA_ID since there are multiple values for Seq_Num for each FA_ID. Do you want to replicate those out so you see all the Reg_Rate values or just get the first one? It seems like in your formula, instead of that 2nd Link=, you need to have a cell reference to whatever cell contains the FA_ID that you are replicating from the EQ_Usage table. Maybe something like this (assuming the replicated FA_ID is in cell K4):=NL("FIRST","EQ_RATE","REG_RATE","JOB_ID",C2,"UM",K5,"LINK=","EQ_SCHD","JOB_ID","=JOB_ID","SEQ_NUM","=SEQ_NUM","FA_ID",K4)
Does this help?
Regards,
Hughes -
Jet Reports Historic Posts Hi,
There are multiple Reg_rates (in the EQ_Rate Table) for each FA_ID (in the EQ_Usage & EQ_Schd Tables) because there are multiple UM (in the EQ_Usage & EQ_Rate Table) and multiple pricing schedules in the EQ_Schd Table. The EQ_Schd table has an effective date for each pricing schedule and the EQ_Usage Table has a work date for each transaction. The Seq_Num is common to both the EQ_Rate & EQ_Schd Tables. There is also a JOB_ID that is common to all my tables.
So I have Replicated the EQ_Usage Table and now need a formula that looks at the EQ_Schd table to find the "last" Seq_num where the FA_ID matches that of the one in the EQ_Usage Table where the Work Date is greater than or equal to the effective date in the EQ_Schd table. Than look at the EQ_Rate Table and return the Reg_rate where the Seq_num matches that of the one found in the Eq_Schd Table and the UM matches that of the one in the EQ_Usage Table.
There are times where an FA_ID can can have two Seq_nums in one pricing schedule just with different UM's, the UM's can be found in the EQ_Rate & EQ_Usage Table.
I hope this is a little more clear….it has again confused me. I'm not sure if I can do this with one formula or two. I can get close using an array formula but the UM throughs me off.
Thanks for your help.
Please sign in to leave a comment.