0

Join data dumps from two companies

Jet Reports Historic Posts

March 25, 2015 18:27

None

Follow

I have successfully joined two summary lists together via NL Filter and NP Union like this:

D5=NL("Filter","G/L Account","No.","No.","10000..39999","Company=","CompanyA")
D6=NL("Filter","G/L Account","No.","No.","10000..39999","Company=","CompanyB")
D7=NL("Rows",NP("Union",D5,D6))

Is it possible to do the same type of thing with a data dump (would be the same formula as above withouth defining a field to return)? When I attempt this by exlcuding "No." in the field column (below) I get #VALUE!

D5=NL("filter","G/L Account",,"No.","10000..39999","Company=","CompanyA")
D6=NL("filter","G/L Account",,"No.","10000..39999","Company=","CompanyB")
D7=NL("Rows",NP("Union",D5,D6))

I could see how it might not be possible because you are technically joining two data sets, not just a column.

Much appreciated!

• Facebook
• Twitter
• LinkedIn

4 comments

• 0
  
  Jet Reports Historic Posts March 25, 2015 21:09

  I have only used the FILTER command a few times, but I think your issue might be that you can't have a filter be the result of a data dump.
  
  If I try this: =NL("FILTER","CUSTOMER","NAME","NO.",W2), the result in my spreadsheet is ||"NL","AllUnique","CUSTOMER","NAME","NO.","THEMEM","",which I recognize as a filter from some of my other uses.
  
  but if I try this: =NL("FILTER","CUSTOMER",,"NO.",W2), it results in the #VALUE! error

• 0
  
  Jet Reports Historic Posts March 25, 2015 21:36

  That is the same thing that I am finding, however thought there might be another way to accomplish it.

• 0
  
  Jet Reports Historic Posts March 26, 2015 14:55

  When you debug a formula like 'NL("filter","G/L Account",,"No.","10000..39999","Company=","CompanyA")' you will see that Jet says the 3rd parameter is mandatory.
  Therefore NL(Filter) can't be used to return keys.
  
  What if you add an extra column that returns the key?
  
  D5=NL("Filter","G/L Account","No.","No.","10000..39999","Company=","CompanyA")
  D6=NL("Filter","G/L Account","No.","No.","10000..39999","Company=","CompanyB")
  D7=NL("Rows",NP("Union",D5,D6))
  
  When you add
  E7=NL(,"G/L Account",,"No.",D7,"Company=","CompanyA")
  F7=NL(,"G/L Account",,"No.",D7,"Company=","CompanyB")
  
  You will have 2 usable keys in every line.
  
  HTH
  rmw

• 0
  
  Jet Reports Historic Posts April 01, 2015 15:12

  Hi,
  
  The below function will allow you to do a union on the record keys. I am using the Cronus demo database so the company will be different than yours, but if you update it then it should work for you.
  
  =NL("Rows",NP("Union",NL("Allunique","G/L Account",,"No.","10000..39999","Company=","CRONUS USA, Inc."),NL("Allunique","G/L Account",,"No.","10000..39999","Company=","CRONUS Mexico S.A.")))
  
  Give this a try and let us know if it worked.