topic Show Frequency (Dimension) in the List Box when using Star Schema in QlikView

Show Frequency (Dimension) in the List Box when using Star Schema

Wed, 03 Nov 2010 20:22:47 GMT

I have Start Schema Data Model contains one Fact Table and couple of Dimensions. I have the Dimension IDs in the fact table and Dimension IDs and Descriptions in the Dimension Tables.

I am trying to use the List Box to show the list of a specific dimension Names and its frequency in the fact Table. But since the Dimension name exists only in the Dimension table, it shows ones all the time.

Could you please help by suggesting a way for doing this?

Show Frequency (Dimension) in the List Box when using Star Schema

pover — Wed, 03 Nov 2010 20:58:56 GMT

If you have QV 10, you could create an expression in the listbox that is a count distinct of the fact table row id's, but in QV 9 you'd probably be better off mapping the dimension value to the dimension ID in the fact table using a "mapping load..." and applymap() so that the list box frequency works.

Since QlikView compresses duplicate data in the same column dimension values will take up about the same space as dimension ids in the fact table.

Regards.

Show Frequency (Dimension) in the List Box when using Star Schema

Thu, 04 Nov 2010 13:27:45 GMT

Thanks Karl for your Response.

here is my comments on the suggestion you have provided

- I still can create expression in QV 9 i do not need 10

- counting the Fact Tables IDs in the List Box expression will not solve the problem ( I have tried it already)

- My Model has about 100 dimensions and writing mapping code of each one will be very expensive

I am trying to find a way to display the frequency of the Dimension in the list box without have to flatten the Model in one table

Show Frequency (Dimension) in the List Box when using Star Schema

Thu, 04 Nov 2010 14:50:25 GMT

Hello,

the frequency is linked to the table you use to display field. So if it stored only once (which is correct for the model) you can't get more.

Now according what you want to display i can offer 2 solutions

1. You need to display frequency in the list box :

you perform a join on the dimension table from fact table ans store the Id under another name (like IdAll to make it unique) You'll get the right frequency but increase the volume of the table

2. You need only the information : you can use a statistic table counting Ids from fact table. The name has to be unique in the model.

best regards

christian

Show Frequency (Dimension) in the List Box when using Star Schema

Thu, 04 Nov 2010 14:58:57 GMT

Thanks Christian for your Response.

1- I am not sure I got your first solution

2- as I have mentioned above Model has about 100 dimensions and creating statistic table counting Ids would be very expensive

Show Frequency (Dimension) in the List Box when using Star Schema

Thu, 04 Nov 2010 17:57:35 GMT

How Do I submit an example file?

Show Frequency (Dimension) in the List Box when using Star Schema

pover — Sat, 06 Nov 2010 22:45:45 GMT

When you reply in the options tab add the file to upload.

Clarifying my points. In QV 9, you can add an expression to be the values of the list, but in QV 10 you can add additional expressions that show up alongside the values in the list in the same way that frequency does in QV 9, but this won't help you if count(Fact Table ID) doesn't work. It should work unless there is something different about your data model.

To map 100 dimensions, you can do an advanced script to loop through all you dimensions to be mapped. In that case it won't be so time consuming.

Regards.