topic Deriving a third Field from 2 tables in QlikView

Deriving a third Field from 2 tables

Tue, 01 May 2012 21:46:27 GMT

Hell Everyone,

I have 2 tables Product and Sales. Each Product ID in Product Table is attached to Multiple SALES ID in Sales table.

PRODUCT TABLE:

SALES TABLE:

I want to create a field in my Product table known as "Has3rdParty" which would have following values corresponding to each Product ID:

1) All 3rd Party: if in the Sales Table the Managed By field has value "Thrid Party" for all the Product ID. example ID = 2

2) Some3rdPArty: id some of the values are Third Party and not all. example ID= 1

3) No 3rdParty: example Id=5

Thanks in advance for any help on this. I'd really appreciate your response.

rwunderlich — Wed, 02 May 2012 03:26:02 GMT

Here's one approach:

LEFT JOIN (ProductTable)

LOAD

ProductId,

if(ManageList='Third Party', 'All 3rd Party'

,if(wildmatch(ManageList, '*Third Party*'), 'Some3rdParty'

,'No 3rdParty'

)) as ManageType

;

LOAD

ProductId,

concat(DISTINCT ManagedBy, ',') as ManageList

RESIDENT SalesTable

Group BY ProductId

;

Example attached.

-Rob

Wed, 02 May 2012 09:36:07 GMT

Thank you Rob, it worked. I really appreciate your response.