
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Generate on the fly tables from existing table
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
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
