Hi again,
Maybe I'm just not awake enough yet, but I can't get my Jet to retrieve distinct suffixes. We're using a dimension code that consists of three letters per contact, followed by a country code, suffixed by a numerical code (about 10 or 15 exist). Now, I want to create a lookup for an option so that a viewer can filter per code. For example, the suffix can be 350 (right(3) of default dimension's no. field) which stands for Foreman (name field in table resource group).
Hence, what I need is a lookup that retrieves all distinct suffixes (i.e. 350, 757, 812 etc.) but of course the right-funtion of excel is interpreted -after- the NL so that's where it gets tricky.
Does anybody have this laying around?
Best,
Chris
Date
Votes
5 comments
-
Jet Reports Historic Posts So you get your Dimension code with a NL or NF function?
If you want to use the right 3 char, and RIGHT is executed after the NL function, I don't really see the problem?
Can you elaborate? If it's a problem, maybe splitting the cells up could work… -
Jet Reports Historic Posts The problem is that if i do it with an excel right-function, i don't get the distinct values, but all occurrances…
So my lookup in the options sheet (if based on "right(3)") would contain several hundred identical values for each distinct possibility, i.e. {350, 350, 350, 754, 757, 757, etc.} instead of {350, 754, 757} (ideally, later i'd substitute this with "foreman", "scaffolding assembler" etc. for the viewer while operating on numerical code, but that's for afterwards. -
Jet Reports Historic Posts Ah I see what you mean now!
The only sollution I can think of:
Run a NL("rows"), and in one column you you'll have your "last 3", that column is the one you use on your lookup:=NL("Lookup";H4:H7;"Row")Where H4:H5 is your column and "Row" is a random name for the column -
Jet Reports Historic Posts Hi Chris,
Have you tried =NL("Lookup";"Dimension value";"=RIGHT(NF(;""Code"");3)";"Dimension code";"NAMEOFDIM";"Headers=";"Select contact")? -
Jet Reports Historic Posts Hi Hans,
My problem was similar to what you describe but slightly different. Your solution made it work though; I ended up using the following (I know, no lookup here, turns out, they didn't want that after all :S):
=NL("Rows=4";"Default Dimension";"=RIGHT(NF(;""No."");3)";"No.";$G$7&"-"&$G$6&"-???";"Company=";$G$5)
Please sign in to leave a comment.