Generate on the fly tables from existing table

[#1]

Hello,

I'm currently extracting all my data to be shown in my QlikView application from a single view in a SQL database. It returns many columns like NotionalAmt, ccypair, strike, tradedate, PnlAmt and so on.. I also have two calendar controls which will be used by users to select a data range to filter the data populated from the db (Please note that this filtering by date is done at the QlikView level).

What I would like to do now is dynamically generate a new table from the existing data QlikView has in memory (Ofcourse this keeps changing based on users selections). The data generated for the dynamic table to be shown in a histogram (barchart) is something as follows

This is a sql query

Select

ROUND(Notional/1000000, .1) as 'Notional',

NULLIF(COUNT(Notional/1000000,0) as Frequency

from myView

GROUP BY ROUND(Notional/1000000, .1)

HAVING COUNT(Notional/1000000) <>0

I do not want to use a SQL or LOAD statement as above. I instead want to use some QlikView functions (Like Aggr()) and set analysis which will help in achieving the same result as the above query does. Any suggestions please with an example?

Cheers,

RC

[#2 · Accepted solution]

Ok, so I managed to find the answer myself. Below is what I did

Dimension:  =Aggr(DISTINCT ROUND( Notional/1000000,.1), Notional)

Expression: =Count(ROUND(Notional/1000000, .1))

I need to actually add an if condition to the Aggr() function to eliminate any NULL values which I haven't done.

Cheers,

RC