topic table- combining dimensions in QlikView

table- combining dimensions

sonikajain — Thu, 03 May 2012 13:02:37 GMT

Hi all,

Please help me in implementing a table where there are 10 rows, 9 of which are pulled from the database, and remianing 1 has to be generated synthetically. This additional row has a different calculation than the rest of the rows.

Please help me in achieving this.

Thanks,

Sonika Jain

table- combining dimensions

Ferran_Garcia_Pagans — Thu, 03 May 2012 17:58:45 GMT

Hi Sonika,

As long as I've understod, you have only one dimension, nine rows are coming from a database and the last row comes from another source.

I would create a table in the data model with 9 rows coming from a database and I would ad a new row to the table.

I've writed an small example:

0- The data:

I've write an excel file with 2 columns 'dimension' and 'value'. The excel has 9 rows.

1- The script.

1.1- Load nine rows from an excel file.

LOAD dimension,

value

FROM

Book1.xlsx

(ooxml, embedded labels, table is Sheet1);

1.2- Add an additional row.

LOAD * INLINE [

dimension, value

10, 10

];

The 2 tables has exactly the same fields and QlikView will create only one table with 10 rows.

2- Visualization, the table.

Now you have a table with 9 rows coming from an excel file and a row loaded inline. To finish this eample you need an expresion for the first 9 row and a diferent expresion for the last row.

I used this expression.

if(RowNo()<=9 and RowNo()>=1, sum(value), value*34)

Now I've a table (graph) with 2 columns and 10 rows. In the dimension 9 rows comes from the excel and the last one is generated in the script. In the values you have an expression for the 9 rows comming from the excel and a different expression for the last one.

table- combining dimensions

mphekin12 — Thu, 03 May 2012 18:33:06 GMT

FYI - To avoid the IF Statement you can do your calculations in your load script.

table- combining dimensions

Ferran_Garcia_Pagans — Fri, 04 May 2012 08:42:19 GMT

If you do the calculation in the load script and yoy don't use an 'if' in the graph you are going to apply the same expression to the additional row, isn't it?

table- combining dimensions

Fri, 04 May 2012 08:50:56 GMT

Hi Sonika,

Can you give a more detailed scenario if the above comment doesn't solve the problem? We don't really get what you want. It is too general.