Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

using rowno() within a concat expression

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;

table4.bmp

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

1 Solution

Accepted Solutions
Highlighted
MVP
MVP

Re: using rowno() within a concat expression

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

4 Replies
mov
Esteemed Contributor III

Re: using rowno() within a concat expression

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

Not applicable

Re: using rowno() within a concat expression

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;

table4.bmp

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

Highlighted
MVP
MVP

Re: using rowno() within a concat expression

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

Not applicable

Re: using rowno() within a concat expression

Awesome, thanks for your help!

Nick