Deriving a third Field from 2 tables

[#1]

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:

PRODUCT ID
1
2
3
4
5
1
2
3
5
6
7
85
4
68

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

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

http://robwunderlich.com

http://qlikviewcomponents.org

[#3]

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