Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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
Thank you Rob, it worked. I really appreciate your response.