Join problem!

valpassos — Sat, 16 Nov 2024 03:00:06 GMT

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

Re: Join problem!

valpassos — Thu, 12 Mar 2020 19:01:33 GMT

Not a hint?

Thanks in advance!

Re: Join problem!

valpassos — Tue, 24 Mar 2020 09:45:12 GMT

Fixed

topic Join problem! in App Development

Join problem!

Re: Join problem!

Re: Join problem!