Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

rileymd88
New Contributor III

Qlikview Data Model: Row or column based expressions?

Hi All,

I am in the process of creating a new data model for a new project and was wondering what the pros and cons would be of using rows or columns for my expressions in my main table. Here is a bit of background info about my project:

  • I am merging together 2-3 sources of data into one large table which will hold key dimension information like the product and day and key expression information like stock, sales and forecasts
  • I will then set up separate master data tables which gives me more information about the products/calendar
  • The majority of data will be coming in on table 1 format ~85%
  • I expect to have about 50 million records per year based on a column based expressions table with 40 columns

Table1 file structure:

Data MeasureProductDayQuantity
StockProduct 121.03.2016102
SalesProduct 121.03.201647
ForecastProduct 121.03.201650

Table2 file structure:

ProductDayStockSalesForecast
Product 121.03.20161024750

What are the pros/cons of having my main table set up like Table1 versus Table2?

Thanks,

R

Tags (1)
1 Solution

Accepted Solutions
MVP
MVP

Re: Qlikview Data Model: Row or column based expressions?

The second structure is a more conventional fact table design and will usually be quicker and easier in terms of the front end expressions. The generic structure of the first table tends to make the front end expressions more complex (as well as  possibly leading to poor performance in a larger data model).

The Generic Load feature in QV will simplify the conversion from the generic form to a flatter fact table form. Search this site for "Generic Load" for more guidance.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
4 Replies
MVP
MVP

Re: Qlikview Data Model: Row or column based expressions?

The second structure is a more conventional fact table design and will usually be quicker and easier in terms of the front end expressions. The generic structure of the first table tends to make the front end expressions more complex (as well as  possibly leading to poor performance in a larger data model).

The Generic Load feature in QV will simplify the conversion from the generic form to a flatter fact table form. Search this site for "Generic Load" for more guidance.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
MVP & Luminary
MVP & Luminary

Re: Qlikview Data Model: Row or column based expressions?

The advantage of the second structure is that you will likely have a smaller document since you will have three fields with probably lower cardinality then if all the quantity values are in one field. And it's easier to do calculations that involve values of the 'same' record, i.e. Stock - Sales for a product over a period. The advantage of the first is that most of your data has that structure.


talk is cheap, supply exceeds demand
MVP
MVP

Re: Qlikview Data Model: Row or column based expressions?

Henric Cronstrom's explanation of generic loading is very helpful:

The Generic Load

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Digvijay_Singh
Honored Contributor III

Re: Qlikview Data Model: Row or column based expressions?

Your second structure appears more connected/familair to dimensional modelling, The facts are expressed against the Product and Day dimensions. The measures can be easily aggregated against dimensions through required expressions with no structural change as you would need in first case using generic load.