using rowno() within a concat expression

[#1]

Hi Everyone,

I've got a problem where i need to be able to have a sequential number appear within a concat expression based on the number of selections made within a field.

For example, i have a field called State which has 30 values. In a straight table i could use the rowno(total) function to give me this as below;

However, i need to show this as part of an expression which is in a text box. Expression is as follows;

"concat(DISTINCT 'f' & State &','& aggr(Distinct State,RowNo(Total)-1) & ',15', '|', fieldIndex('State', State)-1)"

As you can see below, the concat function correctly wraps each of the States in the text, however, the sequential number doesn't show.

fBDF,,15|fCAM,,15|fESS,,15|fHRT,,15|fLUT,,15|fNFK,,15|fPTE,,15|fSFK,,15

I'm assuming this is because it's no longer in a table.

Is there another way around this?

Thanks in advance

[Anonymous]

If the order of the State field in your pivot table is load order, you can use the same fieldIndex():
"concat(DISTINCT 'f' & State &','& fieldIndex('State', State) & ',15', '|', fieldIndex('State', State)-1)"

If not, you can make your State field dual in the script, so that BDF has numeric value 0, CAM - 1, ESS - 2, etc.
If you can do it in the script, the concat will be
"concat(DISTINCT 'f' & State &','& num(State) & ',15', '|', fieldIndex('State', State)-1)"

Regards,
Michael

[#3]

Thanks Michael,

Unfortunatelty, i need the number to be sequential from 0 upwards based on the selection. I've tried using the fieldIndex('State',State) before but it pulls in the value of that state in the whole list of states, so for example, with the selection used above i get;

Similarly, i can't do it in the load in script as i need the values to be sequential, based on an alphabetical sort of the selections made.

Perhaps there anyway of using fieldindex using {$} to only base it on current selection?

Thanks for your help.

Nick

[swuehl · Accepted solution]

I think you can work out a solution by loading in your states in sorted alphabetically (i.e. load order = ordered alphabetically), then use something like

=concat(DISTINCT 'f' & State &','& aggr(rowno(), State) & ',15', '|', fieldIndex('State', State)-1)

The aggr() dimension values will be ordered by load order (should be alphabetical), and the aggr() expression returns the rowno(). Worked on my side. I don't think you need a sort weight in this case.

Hope this helps,

Stefan

[#5]

Awesome, thanks for your help!

Nick