topic Re: What is NoOfColumn() and Column No() ? in QlikView

What is NoOfColumn() and Column No() ?

nikhilgarg — Wed, 01 Jul 2015 09:29:48 GMT

Hello,

Can anyone help me the use of Column No() and No of columns() with example ?

thanks

Re: What is NoOfColumn() and Column No() ?

MK_QSL — Wed, 01 Jul 2015 09:31:30 GMT

From Help FIle

NoOfColumns([total])

Returns the number of columns in the current row segment in a pivot table.

If the pivot table is one-dimensional or if the qualifier total is used as argument, the current row segment is always equal to the entire row.

If the pivot table has multiple horizontal dimensions, the current row segment will include only columns with the same values as the current column in all dimension rows except for the row showing the last dimension in the inter field sort order. The inter field sort order for horizontal dimensions in pivot tables is defined simply by the order of the dimensions from top to bottom.

Example:

if( ColumnNo( )=NoOfColumns( ), 0, after( sum( Sales )))

ColumnNo([total])

Returns the number of the current column within the current row segment in a pivot table. The first column is number 1.

If the pivot table is one-dimensional or if the qualifier total is used as argument, the current row segment is always equal to the entire row.

If the pivot table has multiple horizontal dimensions, the current row segment will include only columns with the same values as the current column in all dimension rows except for the row showing the last horizontal dimension of the inter field sort order. The inter field sort order for horizontal dimensions in pivot tables is defined simply by the order of the dimensions from top to bottom.

Example:

if( ColumnNo( )=1, 0, sum( Sales ) / before( sum( Sales )))

Re: What is NoOfColumn() and Column No() ?

sunny_talwar — Wed, 01 Jul 2015 09:32:41 GMT

I know ColumnNo() is similar to RowNo(), but for a pivot table when you have one or more dimension going across, not sure if I used NoOfColumn() function before.

Re: What is NoOfColumn() and Column No() ?

nikhilgarg — Wed, 01 Jul 2015 09:33:38 GMT

Hey,

I have already gone thru help file but cann't understand much

Re: What is NoOfColumn() and Column No() ?

MayilVahanan — Wed, 01 Jul 2015 09:38:31 GMT

In the below example, u can see, No of columns in the pivot table is 2 (Quarter).

And ColumnNo() is 1 for 1Q15 and 2 for 2Q15.

Quarter	1Q15			2Q15
ID_company	Due	=NoOfColumns()	=ColumnNo()	Due	=NoOfColumns()	=ColumnNo()
1	50000	2	1	50000	2	2
2	100000	2	1	100000	2	2
3	50000	2	1	100000	2	2

Re: What is NoOfColumn() and Column No() ?

sunny_talwar — Wed, 01 Jul 2015 09:40:29 GMT

Here is an example for you to understand:

Table 1 and Table 2 have both the same number of dimensions and expressions, but since in Table 1 there is one dimension (Dim2) is going across, we are able to use ColumnNo() and NoOfColumn() functions, whereas in otherone we don't see any results.

I hope this will clarify things,

Best,

Sunny

Re: What is NoOfColumn() and Column No() ?

nikhilgarg — Wed, 01 Jul 2015 09:45:43 GMT

Hey,

Thanks. This is exactly what happening in my case. My case is same as of you below table. So how can i use ColumnNo() and NoOfColumns() here?

Re: What is NoOfColumn() and Column No() ?

sunny_talwar — Wed, 01 Jul 2015 09:48:12 GMT

If you don't have a dimension going across, you might have to use RowNo() and NoOfRows() instead of ColumnNo() and NoOfColumns(), because the later 2 are used only when a dimension is pivoted (or goes across). Does that make sense?

Re: What is NoOfColumn() and Column No() ?

sunny_talwar — Wed, 01 Jul 2015 09:49:23 GMT

See if this is what you want?

Re: What is NoOfColumn() and Column No() ?

nikhilgarg — Wed, 01 Jul 2015 09:49:55 GMT

Hey,

I tried with ROWNO() and NoOfRows() but it also doesnot work

Re: What is NoOfColumn() and Column No() ?

sunny_talwar — Wed, 01 Jul 2015 09:51:39 GMT

What are the numbers you are expecting to see?

Re: What is NoOfColumn() and Column No() ?

nikhilgarg — Wed, 01 Jul 2015 09:56:32 GMT

I ahve pivot table with 4 dimensions and 8 expresions.

If i have all 4 dimensions expanded then i want expr1 to expr7 visible and dont want expr8 visible.

If i have only first 2 dimensions expanded and rest 2 dimensions colapsed then i want expr1 to expr6 and expr8 visible and don't want expr7 visible.

How can i acheive this?

Thanks

Re: What is NoOfColumn() and Column No() ?

sunny_talwar — Wed, 01 Jul 2015 09:59:34 GMT

Hahahaha, I saw your earlier post, but I don't think I know if a way to make that happen. May be somebody else might now.

I am sorry Nikhil, I wish I could have been more helpful.

Best,

Sunny

Re: What is NoOfColumn() and Column No() ?

nikhilgarg — Wed, 01 Jul 2015 10:02:47 GMT

haha, no prob buddy.

thanks

Re: What is NoOfColumn() and Column No() ?

Wed, 01 Jul 2015 10:18:10 GMT

You could try with Dimensionality() function.

Re: What is NoOfColumn() and Column No() ?

nikhilgarg — Wed, 01 Jul 2015 10:32:32 GMT

I tried but no success

Re: What is NoOfColumn() and Column No() ?

MayilVahanan — Wed, 01 Jul 2015 10:47:20 GMT

As per my knowledge, You can't able to hide the column based on dimensionality of pivot table.

Re: What is NoOfColumn() and Column No() ?

nikhilgarg — Wed, 01 Jul 2015 10:54:44 GMT

Ohk,

Thanks

Re: What is NoOfColumn() and Column No() ?

nikhilgarg — Thu, 02 Jul 2015 07:24:43 GMT

Hey,

In my pivot table, i ahve 4 dimensions and when all 4 dimensions are expanded, i have dimensionality() as 4, 2, 1.

Now, i want sum of 4+2+1 as 7 and i tried:

sum(aggr(Distinct Dimensionality(), SalesRegion, Desk)), but still sum is not 7.

How cld i acheive this?

Re: What is NoOfColumn() and Column No() ?

MK_QSL — Thu, 02 Jul 2015 08:09:13 GMT

Can you provide sample of your Pivot Table?