Multiple rows with different dimensions

[t_donnet]

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:

The model:

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)

Message was edited by: Théo Donnet

[Anonymous]

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!

[sewialwork]

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

[t_donnet]

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

[t_donnet]

Hi Alendra,

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

It could be useful to investigate.

Thanks for your help

[sewialwork]

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]

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.

[t_donnet]

So with one table in load:

(one selection in [Clé rubrique])

Is there any way to to have one row?

[RUBRIQUE] isn't mandatory

[timpoismans · Accepted solution]

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.

[sewialwork]

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