topic add rows in data sets in QlikView

add rows in data sets

priyarane — Fri, 08 Jul 2016 12:21:30 GMT

Hi Community,

How to add rows in data set.

Currently we have data in Pivot table:

and we have to show like below:

it is one of the dimension for ex: Dim1 and Expr: Sum(Sales)

and we have to do in front end only, it is not possible to do in script part.

- Priya

Re: add rows in data sets

boorgura — Fri, 08 Jul 2016 12:24:00 GMT

Do you want to add blank rows? Or just split the column into 2 - separating the number?

Re: add rows in data sets

ThornOfCrowns — Fri, 08 Jul 2016 12:28:40 GMT

Why do you need this?

Re: add rows in data sets

priyarane — Fri, 08 Jul 2016 12:31:53 GMT

I need just 2 blank rows

Re: add rows in data sets

priyarane — Fri, 08 Jul 2016 12:43:57 GMT

sorry it is straight table

Re: add rows in data sets

ThornOfCrowns — Fri, 08 Jul 2016 12:59:23 GMT

Is it just the space you need between the rows? if so you can add chr(10) to add a line break;

Example.

=sum(Sales) & chr(10) & chr(10)

Re: add rows in data sets

priyarane — Fri, 08 Jul 2016 13:04:55 GMT

I don't need spaces and I need rows in between 3 and 4 as shown in the image

Re: add rows in data sets

Anonymous — Fri, 08 Jul 2016 13:06:10 GMT

If it's just for aesthetic reasons another solution could be to wrap the cell text to increase the space between each value. Go to presentation tab > multiline settings and tick the wrap cell text checkbox and then increase the number of lines to suit your requirement.

Re: add rows in data sets

priyarane — Fri, 08 Jul 2016 13:15:37 GMT

we have specific reason to add blank rows, so we need it...

Re: add rows in data sets

ciaran_mcgowan — Fri, 08 Jul 2016 13:17:59 GMT

Unfortunately, there isn't a way to accomplish this in the front end alone. A straight table will display a row for each unique dimension+expression. If there is no dimension, then it cannot display a row.

There are alternatives though, you could add dummy rows in the script and make the text of those rows match the background.

Script: if(Previous(Row)=3 OR Previous(Previous(Row))=3, 1) AS Flag.Blank

Table Background: if(Flag.Blank = 1, White())

Or you could Inline Load a table with Row numbers like 1, 2, 3, 3.1, 3.2, 4, 5. Then your background expression could read if(Row>3 AND Row<4, White()).

As a last resort, you could use an external Excel file to control the table's format, but you can't add a row where there isn't a dimension to display it in the front-end alone. Sorry

Re: add rows in data sets

priyarane — Fri, 08 Jul 2016 13:30:34 GMT

We are getting values from Dimension only, for Ex: Dim1

Re: add rows in data sets

settu_periasamy — Fri, 08 Jul 2016 13:37:43 GMT

May be you can try with valuelist function (if you dimension values are less).

check the attached sample

Re: add rows in data sets

priyarane — Sun, 10 Jul 2016 08:40:17 GMT

Hi Seetu, thanks for your efforts. Is there any way that we can avoid hard coding the values,like in dimension we have taken A, B C D so can't we avoid hard coding of this.

Re: add rows in data sets

settu_periasamy — Sun, 10 Jul 2016 10:14:53 GMT

As i know, You can concat your dimension value and store it as a variable( this value should include single quotes and space character for displaying 2 empty rows - you can see that in my example )..

Use that variable in your valuelist.. then you can do the same in your expression too..

Re: add rows in data sets

priyarane — Mon, 11 Jul 2016 12:33:35 GMT

Hi Ciaran,

as per you I have added it but I am getting out put like

But we need out put like

Dim

Dummy Row1

Dummy Row2

Re: add rows in data sets

priyarane — Mon, 11 Jul 2016 12:35:14 GMT

Code

Re: add rows in data sets

varshavig12 — Tue, 12 Jul 2016 08:32:16 GMT

If your dimension consist of some limited no. of rows, then you can use pick-match.

Re: add rows in data sets

ciaran_mcgowan — Tue, 12 Jul 2016 10:51:16 GMT

Hi Priya,

In your example, you are using the expression

if(Previous(RowNo())=2 OR Previous(Previous(RowNo()))=2, 1 ) AS Flag.Blank

You can't use RowNo() like this, it will always return the value of the row it's reading. You would need to use RowNo() when loading from your data source (in the original table),

Data:
LOAD RowNo() AS  Row,
    Dim,
    Amt
FROM

(ooxml, embedded labels, table is Sheet1);

Then use:

if(Previous(Row)=2 OR Previous(Previous(Row))=2, 1 ) AS Flag.Blank

Although, looking at this again, my other suggestion would serve your needs better. Simply load a Dummy table like this:

DummyData:
LOAD * Inline [
Dim
B1,
B2
];

This creates output of the following table:

Dim	Amt
A	10
B	20
B1	-
B2	-
C	30

Create a Straight Table with Dim & Amt as your Dimensions and this as your Expression:

if(Match(Dim, 'B1', 'B2'),'x',1)

Call the expression Flag and go to the Presentation tab and hide that column. In the Dimensions tab expand each dimension and insert the following code for the Text Color:

=if(Match(Dim, 'B1', 'B2'),White())

See my attached example.

Re: add rows in data sets

ciaran_mcgowan — Wed, 13 Jul 2016 10:21:49 GMT

Hi Priya,

Did any of the suggestions here help you to solve your problem?