how to create calculations based on partial field names

hectorgarcia — Wed, 06 Feb 2013 19:13:07 GMT

I have a table where I have a Column name Customer ID (3 million customers) and for each month i have performed a diferent set of calculations and created as many columns as combinations of calcultaion name &calculation month. for the last 13 Months.

This way instead of having a huge table of 39 million records with the calculations for each customer (6 column table) , each month , with the approach i built , I have a table with 3 million of rows and 91 columns

The size of the application was reduced from 450 Megas to 100 Megas

but now i need to create the GUI objects with calculations but do not know how to perform aggregations based on fieldname patterns.

IE I need to build and expresion to aggregate by customer the authentications by quarter, so I know that the information is in the fields, depending the name of the column I will know that metrics and depending of the numerica part of the column I will know the time dimension

fieldname
cantauth201201
cantauth201202
cantauth201203
cantauth201204
cantauth201205
cantauth201206
cantauth201207
cantauth201208
cantauth201209
cantauth201210
cantauth201211
cantauth201212

this is the table how it looks in QVW.

Any sugestion wiil be more than appreciated

topic how to create calculations based on partial field names in QlikView

how to create calculations based on partial field names