0

Using Grouping Columns=X

Dorka Cruz

September 06, 2017 15:49

None

Follow

I'm hoping to get some direction with the following

I wrote this function:  =NL("columns=8","PBN","PBN","DataSource=","Oracle","GRP_NBR",$G11,"INSURER",$C$5).  It didn’t return all of the PBN’s as you can see in the “PBN Table” below.  I was hoping for it to return PBN 1 – plan type CI; PBN 2 –plan type CI; PBN 3 – plan type CI; PBN 4 – plan type CI; PBN 5 – plan type CI; PBN 2 – plan type II; PBN 4 – plan type II; PBN 5 – plan type II;

 

It only goes to PBN 2 –plan type CI

 

This is the function for plan type: =NL("first","PBN","PLAN_TYPE","DataSource=","Oracle","GRP_NBR",$G11,"INSURER",$C$5,"PBN",$I11)

 

How can I get all of the PBN’s & plan types to appear?

Report result below

 

• Facebook
• Twitter
• LinkedIn

12 comments

• 0
  
  Dorka Cruz September 06, 2017 16:54

  I realized that I wasn't doing this correctly and switched to this function =NL("ROWS=3","INS_GROUP","GRP_NBR","DataSource=","Oracle","INSURER",$C$5,"+GRP_NBR","*")

  so that I can create a primary and secondary list, now the issue is that not all the fields are populating

  The function I have for PLAN_TYPE is =NL("first","PBN","PLAN_TYPE","DataSource=","Oracle","PBN",$I12,"GRP_NBR",$G11,"INSURER",$C$5), why does it populate for the first row (pbn 1) but not for the others 2,3,4 etc?

  

   

   

• 0
  
  Heather Rowe September 06, 2017 16:54

  Please forgive me if this seems like an obvious question, but since I cannot verify this in the snapshot we see of your data...

  are you 100% certain that all PBN's & Plan Types match the filters you have set for both GRP_NBR and INSURER?

  EDIT - please disregard - new post coming in response to poster's new inforamtion

  Edited by Heather Rowe September 06, 2017 16:57
• 0
  
  Heather Rowe September 06, 2017 17:00

  I can't see the cell references you are using, so making some assumptions here

  your row references probably need to be locked - for instance I assume that your PBN reference in I12 does not also appear in I13, as the formulas replicate down, the references move unless you have locked them.

  You have locked the reference to the INSURER (C5), but not for PBN or GRP_NBR

• 0
  
  Dorka Cruz September 06, 2017 17:01

  I'm including a snapshot of the spreadsheet so that you can see where the cell is referring to:

  

• 0
  
  Heather Rowe September 06, 2017 17:22

  I'm assuming that in I12, you have another ROWS function, replicating the PBN numbers?

  if so, here is how your formula in J12 is replicating....

  in row 12, it looks like this: =NL("first","PBN","PLAN_TYPE","DataSource=","Oracle","PBN",$I12,"GRP_NBR",$G11,"INSURER",$C$5), this is fine because I12= "1", G11="GG-196B", and C5="P"

  but in row 13, it look like this:

  =NL("first","PBN","PLAN_TYPE","DataSource=","Oracle","PBN",$I13,"GRP_NBR",$G12,"INSURER",$C$5), I13="2" (presumably), G12=BLANK, and C5="P"

  so it's the blank in G12 that we need to deal with - now, if you don't need to replicate the Group Number down, then no problem, just lock the referenced to G12 like this $G$12

  But generally, that's not what you need in Jet - you need to replicate the primary row as well as rows inside of the group (I'm assuming that is what you are doing here based on the "ROWS=3" function mentioned above).  You have two solutions (the only difference is appearance).

  In either case, you need to repeat the GRP_NBR on each row, and update your reference in J12 and possibly some others to point the reference on the ROW (similar to what you are doing with PBN)

  Option 1 = repeat GRP_NBR in column G = just by putting "=G11" into cell G12, then that will replicate down on each row within the rows grouping.  (it will show on every row of your final output)

  Option 2 = move the rows reference to a hidden column, put a repeat in the row below it as described above, and then reference the GRP_NBR for your primary row only in column G.   If you use this option, I suggest wrapping it into a Jet formula so it will not need to recalculate every time you open your report.  Example: if you move the ROWS function to column C, then G11 would be =C11&NP("EVAL","=" " " " " )  spaces added to the 'EVAL' formula for demonstration purposes.

• 0
  
  Dorka Cruz September 06, 2017 17:22

  I tweaked it further and I’m almost there, but I can’t get all of the “PLAN_TYPE” to appear.   Only the “CI” plan_type is appearing as you can see from the snapshots.  Please see original post which shows the PBN table with all of the plan types for group # GG-524NY.

  
  
  
  

• 0
  
  Dorka Cruz September 06, 2017 17:28

  Shouldn't the secondary NL("Rows",,,,) give me all of the PBNs for the group # referenced in column F?

  

   

  Edited by Dorka Cruz September 06, 2017 17:32
• 0
  
  Heather Rowe September 06, 2017 18:34

  Regarding your question on PLAN_TYPE, I understand that your screenshot of the PBN table is showing everything related to GRP_NBR "GG-196B", but do they also all correspond to INSURER "P"?  Because INSURER is part of your filter for that Row, it has to be part of all of the PBN records.

  This formula NL("rows","PBN","PBN","DataSource=","Oracle","INSURER",$C$5,"GRP_NBR",$F12,"+PBN","*")

  Will only return PBN numbers from the PBN Table if all of the following is true:

  DataSource = Oracle (in formula); INSURER = "P" (cell reference); GRP_NBR = "GG-196B" (replicating cell reference), PBN = any (in formula)

   

• 0
  
  Dorka Cruz September 06, 2017 19:11

  Yes I just didn't include in the screenshot but here it is, in the table below there is only plan_type CI but in other groups there are more than CI as the plan type which is not coming out in the results of the report as shown in my previous post for GG-524NY

  

• 0
  
  Heather Rowe September 06, 2017 20:14

  This has me stumped - I'm starting to think that somehow their is a piece missing from the table records, even though your screenshot shows that they are all together.  If my suspicion is true, it almost has to be the INSURER field.

  What if you try replicating the ROWS formula for the PBN table without the INSURER filter - is there any way that will return additional information that is not desired for this report?

  if anyone else has suggestions, please feel free to jump in on this... :)

• 0
  
  Dorka Cruz September 08, 2017 13:16

  I got it figured out, I had to create a data dump and add all the fields that I would need in the field parameter since the PBN table doesn't have a primary key.  Then from their I was able to use the NF function to pull the other fields.  Thanks for all of your assistance.

  Data Dump function: =NL("Rows","PBN",{"CAP_RATE","GRP_NBR","INSURER","PBN","PREMIUM","PLAN_TYPE"},"DataSource=","Oracle","INSURER",$C$5,"GRP_NBR",$F12,"+PLAN_TYPE","*","+PBN","*")

• 0
  
  Heather Rowe September 08, 2017 16:17

  I'm glad you were able to find a solution Dorka, have a great weekend.