0

Compare

Jet Reports Historic Posts

December 17, 2012 10:20

None

Follow

For better evaluation I use a Distribution Code as a Dimension (Dimension Code: VER-GEB) on my customers . The Dimension Value Code is always AUS_[Country Code], for example AUS_IT for Italy. In the customer table there is the regular Country/Region Code (like IT for Italy).
From time to time I would like to compare this Dimension Value Code with the Country/Region Codeon the customer table to show me incorrect entries. Unfortunately I have no idea how to compare the expression AUS_IT with IT.

Here my code (german):
=NL("Zeilen";"Debitor";"Nr.";"Limit=";20;"Länder-/Regionscode";"<>CH";"Link=";"Standarddimension";"Nr.";"=Nr.";"Dimensionscode";"VER-GEB";"Dimensionswertcode";"=""AUS_"&"Länder-/Regionscode")
With this code, Excel unterstand to set the filter like Dimension Value Code = AUS_Country/Region Code. Is there a possibility to set the filter like Dimension Value Code = AUS_[Country/Region Code], also AUS_IT?
Thanks for your help in advance.

• Facebook
• Twitter
• LinkedIn

4 comments

• 0
  
  Jet Reports Historic Posts December 17, 2012 14:18

  Hi Dave,
  
  If I understand you correctly, your problem should be solved by using "|", so that the code become:
  
  =NL("Zeilen";"Debitor";"Nr.";"Limit=";20;"Länder-/Regionscode";"<>CH";"Link=";"Standarddimension";"Nr.";"=Nr.";"Dimensionscode";"VER-GEB";"Dimensionswertcode";"=AUS_IT|""AUS_"&"Länder-/Regionscode")
  Andy

• 0
  
  Jet Reports Historic Posts December 17, 2012 15:15

  Hi Andy,
  Unfortunately it doesn't work.
  My goal is to combine the term "AUS_" with the query country/Region code.
  
  Excel do: AUS_COUNTRY/REGION CODE (that results in a Invalid field-error)
  I want: AUS_IT (example for Italy)
  
  Does that work?

• 0
  
  Jet Reports Historic Posts December 18, 2012 06:18

  Hi Dave,
  
  Try using NL(First) function to substitute the "Länder-/Regionscode", so for example:
  
  =NL("Zeilen";"Debitor";"Nr.";"Limit=";20;"Länder-/Regionscode";"<>CH";"Link=";"Standarddimension";"Nr.";"=Nr.";"Dimensionscode";"VER-GEB";"Dimensionswertcode";"AUS_"&NL("First";[your country table];"Länder-/Regionscode";[add your filters here])

• 0
  
  Jet Reports Historic Posts December 19, 2012 10:51

  OK. The solution is:
  
  - B1 Hide+?
  - B3 =IF(E3=F3;"Hide";"Show")
  - C3 =NL("Zeilen";"Debitor";"Nr.";"Limit=";20;"Länder-/Regionscode";"<>CH")
  - D3 =NF($C3;"Nr.")
  - E3 =NF($C3;"Länder-/Regionscode")
  - F3
  = NL("First";"Standarddimension";"Dimensionswertcode";"Nr.";$D3;"Dimensionscode";"VER-GEB")
  
  Thanks to hansfousert