Join problem!

[valpassos]

Hi community!,

I have a (small) modeling issue I wanted to ask your help for.

I want to achieve the _Used? flag column below in my model:

I already have the Local Product ID column and Global Product ID in the same table.
Local Products and Global Products share many relationships between then, and there is a specific relationship I want to flag as (non)existing for each Local Product ID & Global Product ID: the 'Used' relationship.

So, for each row, I want to have the _Used? flag with 0 or 1: 0 means the relationship doesn't exist; 1 means it does.

I have a Relationships table that tells me the relationships they share, but I don't know how to join these tables to achieve the _Used? flag.

This is what I have:

NOCONCATENATE
Relationships:
%ID_Relationship_Local_Product
%ID_Relationship_Global_Product,
Relationship_Type,
...
...
FROM MyRelationshipsTable.QVD
(qvd);


NOCONCATENATE
LocalGlobalProduct:
LOAD
%ID_Relationship_Local_Product
%ID_Global_Product,
Local_Product_Name,
Global_Product_Name,
...
...
FROM MyProductTable.QVD
(qvd);

These two tables are linked by %ID_Relationship_Local_Product (or shouldn't be?).

To come up with the _Used? flag, I tried this:

LEFT JOIN(LocalGlobalProduct)

LOAD
%ID_Relationship_Local_Product,
IF(ISNULL(ApplyMap('MapUsedRelationshipBetweenLocalAndGlobalProduct',%ID_Relationship_Local_Product,NULL())),'0','1') AS _Used?
RESIDENT LocalGlobalProduct;

The mapping 'MapUsedRelationshipBetweenLocalAndGlobalProduct' derives from the Relationships table (given the Local Product iID, it tells me the Global Product it uses).

But this solution doesn't work, as it is putting '1' for every global product, not just for the ones where the relationship exists. And the '0' doesn't work as well.

What am I doing wrong?

Thank you!,
Lisa

[valpassos]

Not a hint?

Thanks in advance!

[valpassos · Accepted solution]

Fixed