topic Re: Count of Columns in table in QlikView

Count of Columns in table

Mon, 16 Feb 2015 07:34:38 GMT

Hi,

I need to find out count of column numbers in a table, can any one please help me find the right way.

I have a dataset which contains weight data of 100 individuals being weighed across a period of n number of weeks.

I need to analyse weekly data for each participant & form a trend line chart.

The data comes in every week, so every week a new column will get added, how can I deal with this ?

Thanks in advance

Regards

Swapneel

Re: Count of Columns in table

AbhijitBansode — Mon, 16 Feb 2015 07:48:27 GMT

If you are loading data from ODBC database, try utilizing SQLTABLES and SQLCOLUMNS statements in the script.

Re: Count of Columns in table

tobias_klett — Mon, 16 Feb 2015 07:49:40 GMT

Hi,

just Count the fields of your tablexy like this:

Count({1<$Table={tablexy}>} $Field)

Hope this helps

Regards

Tobias

Re: Count of Columns in table

Mon, 16 Feb 2015 07:56:01 GMT

@Tobias Klett

Thanks for your quick reply, can you please elaborate how do I deal with the issue that every week a new column is being added with week number.

How can I add that to my calculations, making it generic, something to be done in scripting ?

Thanks,

Regards

Swapneel

Re: Count of Columns in table

Mon, 16 Feb 2015 07:57:35 GMT

Thanks Tobias Klett for your quick reply,

Can you please elaborate how do I deal with the issue that every week a new column is being added with week number.

How can I add that to my calculations, making it generic, something to be done in scripting ?

Thanks,

Regards

Swapneel

Re: Count of Columns in table

Colin-Albert — Mon, 16 Feb 2015 08:01:28 GMT

You can use the CROSSTABLE function to unpivot the data whist loading, so your table structure is constant with participant, weight and date, rather than adding extra columns each time.

Re: Count of Columns in table

tobias_klett — Mon, 16 Feb 2015 08:17:54 GMT

Hi Swapneel,

you should us the crosstable() function in your script to bring the column Labels in one column and the values into the other. Name the column with the column Labels week and the other value like this:

tablexy:

crosstable(Week, Value, 3)

load *

resident...

NOTE: There are 3 columns with Dimensions Prior to the weekly added columns. Make sure those are the first ones in your table.

Regards Tobias

Re: Count of Columns in table

Colin-Albert — Mon, 16 Feb 2015 08:31:34 GMT

There is a good blog about using crosstable here.

http://community.qlik.com/blogs/qlikviewdesignblog/2014/03/24/crosstable

Re: Count of Columns in table

Colin-Albert — Mon, 16 Feb 2015 20:14:24 GMT

Hi Swapneel,

Crosstable will not create a new column each week, it will convert the week columns into rows which are much easier to process.

Read the crosstable blog which explains the process. See the link I posted earlier.

Re: Count of Columns in table

Tue, 17 Feb 2015 05:56:58 GMT

Thanks for your helpful comments & tips

@ Colin Albert & @ Tobias Klett

Issue has been solved

Re: Count of Columns in table

Amit_Kakkad_PD — Tue, 27 Feb 2024 09:08:26 GMT

Great answer! Just what I wanted!