topic Re: Auto row number for a straight table based on expressions applied in QlikView

Auto row number for a straight table based on expressions applied

Mon, 04 Jan 2016 21:40:54 GMT

I am trying to create an auto number for my rows under a table using RowNo(), but I am not getting what I want. The original table is only showing the values that match my expressions and what I want is for the RowNo() to generate only for those values that are shown in the table and that match the expression. I want to use the RowNo() as a sort of alert unique number for that data value.

When I place the RowNo() it brings all the values for my data, not only the ones that are true due to the calculations, and it also repeats the number is some instances. Does anybody know how I can generate a true RowNo() for only the rows that are showing in the table due to the expressions I applied? I can provide a detail of the data if needed. Thank you.

Re: Auto row number for a straight table based on expressions applied

settu_periasamy — Tue, 05 Jan 2016 02:10:41 GMT

Hi,

Can you provide sample data?

May be try this expression...

=if(not isnull([YourExpression Label Name],RowNo(TOTAL))

Re: Auto row number for a straight table based on expressions applied

sunny_talwar — Tue, 05 Jan 2016 02:18:12 GMT

Would be good to know how many dimensions you have and what expressions do you have in your chart?

Re: Auto row number for a straight table based on expressions applied

HirisH_V7 — Tue, 05 Jan 2016 04:28:27 GMT

Hi ,

Post your sample app-Data or how many dimensions your using ??

-Hirish

Re: Auto row number for a straight table based on expressions applied

ramasaisaksoft — Tue, 05 Jan 2016 05:26:18 GMT

Hi Maria,

see the below thread will help to you by using Row no()

Row number in straight table

Re: Auto row number for a straight table based on expressions applied

Tue, 05 Jan 2016 14:54:03 GMT

Thank you to everyone that replied. So I have 2 tables. In two different applications that I want the row number to generate in the same way.

The simplest table has 3 dimensions and 3 expressions (adding one with the Row Number)

The more complicated table has 3 dimensions and 6 expressions (adding one with the Row Number)

What I want is to calculate the column shown in Red in the table below. When I use either the RowNo() or the RowNo(TOTAL) function it brings back all the relationship numbers that do not meet my expressions and gives them a row number. I do not want this. And even when I hide the null value in some rows or use the

=if(not(isnull([Relationship Number])),RowNo(TOTAL)) expression, it generates the number up to 65, where I only want it to be max 19 in this case since only 19 relationship numbers meet my expressions.

The expressions that I have are the following:

The fields you see me using in my expressions where calculated within my script.

For the Relationship Number - I have the RELATIONSHIP_NUMBER field also as a dimension but it is hidden in the straight table. I added it here as an expression as well in order to get the unique count of alerts by relationship number.

(TotalTran > '0' and RISK_RATING = '01' and TotalAmount > BAL_Threshold_LOW, RELATIONSHIP_NUMBER,
IF(TotalTran > '0' and RISK_RATING = '02' and TotalAmount > BAL_Threshold_MED, RELATIONSHIP_NUMBER,
IF(TotalTran > '0' and RISK_RATING = '03' and TotalAmount > BAL_Threshold_HIGH, RELATIONSHIP_NUMBER,
IF(TotalAmount > '1000' and RISK_RATING = '01' and TotalTran >= TRAN_Threshold_LOW, RELATIONSHIP_NUMBER,
IF(TotalAmount > '1000' and RISK_RATING = '02' and TotalTran >= TRAN_Threshold_MED, RELATIONSHIP_NUMBER,
IF(TotalAmount > '1000' and RISK_RATING = '03' and TotalTran >= TRAN_Threshold_HIGH, RELATIONSHIP_NUMBER))))))

For Balance Exception

(TotalTran > '0' and RISK_RATING = '01' and TotalAmount > BAL_Threshold_LOW, 'Exception - Bal',
IF(TotalTran > '0' and RISK_RATING = '02' and TotalAmount > BAL_Threshold_MED, 'Exception - Bal',
IF(TotalTran > '0' and RISK_RATING = '03' and TotalAmount > BAL_Threshold_HIGH, 'Exception - Bal')))

For Tran Exception

(TotalAmount > '1000' and RISK_RATING = '01' and TotalTran >= TRAN_Threshold_LOW, 'Exception - Tran',
IF(TotalAmount > '1000' and RISK_RATING = '02' and TotalTran >= TRAN_Threshold_MED, 'Exception - Tran',
IF(TotalAmount > '1000' and RISK_RATING = '03' and TotalTran >= TRAN_Threshold_HIGH, 'Exception - Tran')))

Bank	Risk	Relationship Number	Balance Exception	Tran Exception	Unique ID	RowNo(TOTAL)
Alert Totals		19	14	10
1	LOW	123	Exception - Bal	-	1	2
1	LOW	234	Exception - Bal	-	2	7
1	LOW	345	Exception - Bal	-	3	12
1	LOW	489	Exception - Bal	-	4	13
1	LOW	552	Exception - Bal	-	5	14
1	MEDIUM	678	Exception - Bal	Exception - Tran	6	19
1	MEDIUM	789	-	Exception - Tran	7	24
1	MEDIUM	899	Exception - Bal	-	8	25
1	MEDIUM	911	Exception - Bal	-	9	26
1	MEDIUM	1025	Exception - Bal	-	10	30
1	HIGH	1136	-	Exception - Tran	11	31
2	MEDIUM	1285	Exception - Bal	Exception - Tran	12	44
2	MEDIUM	1378	Exception - Bal	Exception - Tran	13	47
2	MEDIUM	1452	Exception - Bal	-	14	54
2	MEDIUM	1589	Exception - Bal	Exception - Tran	15	61
2	HIGH	1696	-	Exception - Tran	16	62
2	HIGH	1778	Exception - Bal	Exception - Tran	17	63
2	HIGH	1823	-	Exception - Tran	18	64
2	HIGH	1911	-	Exception - Tran	19	65

Re: Auto row number for a straight table based on expressions applied

Anonymous — Tue, 05 Jan 2016 17:34:50 GMT

hi maria,

Maybe in expression tab Select option expression total instead of max rows under total mode as per your requirement

Re: Auto row number for a straight table based on expressions applied

settu_periasamy — Wed, 06 Jan 2016 05:20:16 GMT

Not sure, how to exclude the rows using the if condition. Normally, it would be,

=Aggr(RowNo(TOTAL),Dim1,Dim2,Dim3) - in this need to use the column is not null.

I tried, but not able to achieve the result.