Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I'm sure this question has come up before and I'm almost embarrassed for asking it. But it's baffling me.
Suppose I have a table of companies (C1, C2, C3, C4, C5, C6) and a second table, mapping companies to head office.
e.g.
Company Head office
C1 HO1
C2 HO1
C3 HO2
C4 HO1
C4 HO2
Note that a company can map to more than one head office in my example. Also, C5 and C6 have no head office.
If I create a table with two columns, Company and Head office, then I'll get a nice list of the mappings, and there will be a '-' for the missing data against C5 and C6. Now, if I filter on Head office by choosing NOT HO2, then the result will only show me HO1. C5 and C6 will no longer be included because there are no associated records in the HO table.
On one hand this makes sense to me, but on the other, surely filtering NOT HO2 should also include those records for which there is no head office. Hopefully, the example attached might make more sense.
One thing I could do is create a mapping for all the companies without a head office (e.g. add C5 and C6 to the map, but call the head office "NA" or something. This would work, I'm sure, but in my live example, there are close to 100,000 rows of data and I'm reluctant to generate so many extra rows if there's a better way of doing it.
Hope this makes sense.
Thanks
James
One way you could change this is a resident load. That way it would use the same entries already stored in memory and map the N/A to each of the companies of your choosing.
Joined:
LOAD * INLINE [
Company
C1
C2
C3
C4
C5
C6 ];
left join
LOAD * INLINE [
Company , HeadOffice
C1,HO1
C2,HO1
C3,HO2
C4,HO1
C4,HO2
];
MapFinal:
LOAD Company,
IF(isNull(HeadOffice), 'N/A',HeadOffice)as HeadOffice,
' ' as Junk
Resident Joined;
Drop table Joined;
Attached is an example of this, even for 100,000 rows it shouldn't take that much longer.
Hope this helps!
-Brandon
One way you could change this is a resident load. That way it would use the same entries already stored in memory and map the N/A to each of the companies of your choosing.
Joined:
LOAD * INLINE [
Company
C1
C2
C3
C4
C5
C6 ];
left join
LOAD * INLINE [
Company , HeadOffice
C1,HO1
C2,HO1
C3,HO2
C4,HO1
C4,HO2
];
MapFinal:
LOAD Company,
IF(isNull(HeadOffice), 'N/A',HeadOffice)as HeadOffice,
' ' as Junk
Resident Joined;
Drop table Joined;
Attached is an example of this, even for 100,000 rows it shouldn't take that much longer.
Hope this helps!
-Brandon
Great solution, thanks Brandon!