Count of Columns in table

[#1]

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

[AbhijitBansode]

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

[tobias_klett]

Hi,

just Count the fields of your tablexy like this:

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

Hope this helps

Regards

Tobias

[#4]

@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

[#5]

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

[Colin-Albert]

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.

[tobias_klett · Accepted solution]

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

[Colin-Albert]

There is a good blog about using crosstable here.

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

[Colin-Albert]

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.

[#10]

Thanks for your helpful comments & tips

@ Colin Albert & @ Tobias Klett

Issue has been solved