Two facts, different dimensions

[valpassos]

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

[pradosh_thakur]

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

[agigliotti]

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

ex.

load * from Fact1;

join

load * from Fact2;

[valpassos]

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]

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]

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]

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

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

hope it helps.

[sunny_talwar]

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 · Accepted solution]

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]

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