Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Save $300 - Register for QlikWorld (formerly Qonnections) by January 31st: Learn More
Partner
Partner

Multiple rows with different dimensions

Hello,

Row will duplicate for the same dimensions and have one calculate measure on different line.

I would like to know what is the best way to have a row with the three measures.

[UPDATE]

The chart table have only 6 dimensions.

Here the script:

script.png

The model:

model.png

I ve been in charge of this app that I haven't develop.

Thanks for your help/advice

Regards

QlikSense November 2017

(First 3 cols are dimensions, followed by 3 measures)

example.png

Message was edited by: Théo Donnet

1 Solution

Accepted Solutions
timpoismans
Valued Contributor

Re: Multiple rows with different dimensions

In your original post here, there was a load statement of the Rubrique-table.

You had three expressions in there to fill the new fields [Composition], [Largeur], [Poids Net].

As you want to create a new field (column) based on the value of [Rubrique], you'd have to make them in a seperate table.

I don't know if this is the best option, but it works from a quick test I did.

The following is purely example code, but I tried to re-use your fields' names. It does make use of somewhat simplified tables. Main in your main table, the others are tables used to get the desired result.:

Main:

LOAD Distinct

[Clé rubrique]

FROM [Insert TableName];

//Composition:

Left Join(Main)

LOAD

[Clé rubrique],

RBQVAL AS Composition

FROM [Insert TableName]

Where Rubrique = 'COMPO';

//Largeur:

Left Join(Main)

LOAD

[Clé rubrique],

RBQVAL AS Largeur

FROM [Insert TableName]

Where Rubrique = 'WIDTH';

//PoidsNet:

Left Join(Main)

LOAD

[Clé rubrique],

RBQVAL AS [Poids Net]

FROM [Insert TableName]

Where Rubrique = 'WEIGHT2';

What this does require is that you don't have the field [Rubrique] in your main table, as this is the field that is causing the null-value lines.

I suggest you try it out in the app with just the one table in. In case it doesn't work, you can always post the "one table in load" application and I can help you out.

View solution in original post

11 Replies
Partner
Partner

Re: Multiple rows with one different measure

Try to use the PIVOT TABLE.

That you can choose by Row, Column AND Measure. That way you can achieve what you want.

Like and mark as helpful if it helped.

Thanks!

Partner
Partner

Re: Multiple rows with one different measure

Hello!

Are you sure in your data model? Looks like you concatenate some tables without any common fields, or maybe in this table you use data from unrelated tables. This can cause duplicate rows and blank fields.

Hope that will help)


AGB

Partner
Partner

Re: Multiple rows with different dimensions

Hi Felipe,

I tried with the pivot table and I didn't work. I found out that it was only dimensions.

Look back the post. I've just updated with new info.

Thanks

Partner
Partner

Re: Multiple rows with different dimensions

Hi Alendra,

Look back the post. I've just updated with new info.

It could be useful to investigate.

Thanks for your help

Partner
Partner

Re: Multiple rows with different dimensions

Add hash keys (cie rubrique, cie categorie and so on) into your table and check which one of them is different for the same value (which can cause fields duplication). You can also try to add other fields in the table to find one that will be having 3 different values for your duplicating rows.

I am almost completely sure your problem is in the data model. Main purpose now is to examine your tables with hash keys: you need to find either multiple rows with one key or (most likely in your Article table) wrong combinations or hash keys from other tables.

Hope that will help)

AGB

timpoismans
Valued Contributor

Re: Multiple rows with different dimensions

The problem has to be in the data model as you said.

But I'd say his [Clé rubrique] isn't unique and each [Clé rubrique] has multiple [Rubrique] values. This causes the load statement in the original post to cause the situation he shows in the table (Multiple null-values per [Valeur rubrique]).

t.donnet You can simply test this by creating a table with the following fields: [Clé rubrique], [Rubrique] (, [Composition], [Largeur], [PoidsNet].)

If this shows more than one [Rubrique] per [Clé rubrique], this is the problem.

Partner
Partner

Re: Multiple rows with different dimensions

So with one table in load:

(one selection in [Clé rubrique])

tab rubrique.png

Is there any way to to have one row?

[RUBRIQUE] isn't mandatory

timpoismans
Valued Contributor

Re: Multiple rows with different dimensions

In your original post here, there was a load statement of the Rubrique-table.

You had three expressions in there to fill the new fields [Composition], [Largeur], [Poids Net].

As you want to create a new field (column) based on the value of [Rubrique], you'd have to make them in a seperate table.

I don't know if this is the best option, but it works from a quick test I did.

The following is purely example code, but I tried to re-use your fields' names. It does make use of somewhat simplified tables. Main in your main table, the others are tables used to get the desired result.:

Main:

LOAD Distinct

[Clé rubrique]

FROM [Insert TableName];

//Composition:

Left Join(Main)

LOAD

[Clé rubrique],

RBQVAL AS Composition

FROM [Insert TableName]

Where Rubrique = 'COMPO';

//Largeur:

Left Join(Main)

LOAD

[Clé rubrique],

RBQVAL AS Largeur

FROM [Insert TableName]

Where Rubrique = 'WIDTH';

//PoidsNet:

Left Join(Main)

LOAD

[Clé rubrique],

RBQVAL AS [Poids Net]

FROM [Insert TableName]

Where Rubrique = 'WEIGHT2';

What this does require is that you don't have the field [Rubrique] in your main table, as this is the field that is causing the null-value lines.

I suggest you try it out in the app with just the one table in. In case it doesn't work, you can always post the "one table in load" application and I can help you out.

View solution in original post

Highlighted
Partner
Partner

Re: Multiple rows with different dimensions

This can be an option, yes

Or you can use Crosstable (https://help.qlik.com/en-US/sense/June2018/Subsystems/Hub/Content/LoadData/work-with-cross-tables.ht...)  to revert Clé rubrique-Rubrique-Composition-Largeur-Poids Net data set.


Hope this will help!


AGB