Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

valpassos
New Contributor III

Two facts, different dimensions

Hi community

I've posted this question in a comment but maybe I will have more luck if I post it as a general question.

So, I have a fact table with everything concatenated into it. You can see a glimpse into my Customer and Warehouse values, and the facts they are producing.

(image deleted)

You can see that Fact2 is only at the level of the Warehouse, and Fact1 is at the level of both the Customer and the Warehouse.

My Customer and Warehouse values come in fact from the same column in the source table, but they produce different facts, and in the qlik script I'm just doing a WHERE filter on their ID code to know what is a Customer and what is a Warehouse, and them I'm joining them into one table.

The thing is, I have a situation where I need to have the Fact2 in the same line, associated with the Customer, because I need to compare these two facts in a scatter plot where I'm using Customer as a dimension. So, I need to have that 10 score in fact2 in every line for the same Warehouse but different Customer, as I've drawn in the image.

Can someone help me?

Thanks!,

Lisa

1 Solution

Accepted Solutions
valpassos
New Contributor III

Re: Two facts, different dimensions

Hi Thomas,

My mistake when I was transcribing my script here. The tables share indeed some fields and eventually I solved the issue by left joining the Customer table to the Warehouse table. Like this:

Customer:

%ID

%ID_Warehouse

...

Fact1

FROM Source

WHERE %ID=... (code that gives me only Customers)

LEFT JOIN

Warehouse:

%ID              AS               %ID_Warehouse

Fact2

From Source

WHERE %ID=... (code that gives me only Warehouses)

10 Replies
pradosh_thakur
Valued Contributor III

Re: Two facts, different dimensions

A little more information please.. May be a sample app.

agigliotti
Honored Contributor II

Re: Two facts, different dimensions

i think you should join the two tables Fact1 and Fact2 rather than concatenate them.

ex.

load * from Fact1;

join

load * from Fact2;

valpassos
New Contributor III

Re: Two facts, different dimensions

Hi Andrea,

They are joined, exactly like you described. But then they are concatenated into a giant fact table. Like this:

Warehouse:

Field1

Field2

Fact2

From Source

WHERE %ID=... (code that gives me only Warehouses)

;

JOIN(Warehouse)

Customer:

Field3

Field4

Field5

Fact1

FROM Source

WHERE %ID=... (code that gives me only Customers)

;

CONCATENATE(MainFactTable)

Load *

resident Warehouse

;

valpassos
New Contributor III

Re: Two facts, different dimensions

Hi Pradosh,

Maybe my script logic below will help?

Warehouse:

Field1

Field2

Fact2

From Source

WHERE %ID=... (code that gives me only Warehouses)

;

JOIN(Warehouse)

Customer:

Field3

Field4

Field5

Fact1

FROM Source

WHERE %ID=... (code that gives me only Customers)

;

CONCATENATE(MainFactTable)

Load *

resident Warehouse

;

thkarner
Contributor III

Re: Two facts, different dimensions

Hi,

you´re joining table Warehouse with table Customer, but there is no single field which has the same name and therefore no join will be done. You´ll get the "carthesian-product" of both tables which means all combinations. All rows from table warehouse multiplied with all rows from table customer.

Not sure what you want to achieve, but probably JOIN is not the right approach.

BR

Thomas

agigliotti
Honored Contributor II

Re: Two facts, different dimensions

maybe you can use the below expression to fill Fact2 column when is null:

if( isnull(Fact2), above(Fact2), Fact2 )


hope it helps.

MVP
MVP

Re: Two facts, different dimensions

Is Fact2 a field? you can try this

If(Len(Trim(Customer)) > 0, Avg(TOTAL <Warehouse> Fact2))

or

If(Len(Trim(Customer)) > 0, Sum(TOTAL <Warehouse> Fact2))

valpassos
New Contributor III

Re: Two facts, different dimensions

Hi Thomas,

My mistake when I was transcribing my script here. The tables share indeed some fields and eventually I solved the issue by left joining the Customer table to the Warehouse table. Like this:

Customer:

%ID

%ID_Warehouse

...

Fact1

FROM Source

WHERE %ID=... (code that gives me only Customers)

LEFT JOIN

Warehouse:

%ID              AS               %ID_Warehouse

Fact2

From Source

WHERE %ID=... (code that gives me only Warehouses)

valpassos
New Contributor III

Re: Two facts, different dimensions

Hi Andrea,

Yours seems a short-term solution. I eventually solved the issue by left joining the two tables. Like this:

Customer:

%ID

%ID_Warehouse

...

Fact1

FROM Source

WHERE %ID=... (code that gives me only Customers)

LEFT JOIN

Warehouse:

%ID              AS               %ID_Warehouse

Fact2

From Source

WHERE %ID=... (code that gives me only Warehouses)

Lisa