Populating missing entries in table

[#1]

Hi all,

I have an issue with how data is represented when joining two tables, mostly caused by the output from the database I'm reading which I have no way of changing. I think my script is going to have to be a little more involved than a basic join but I just can't figure it out.

Say I have Locations:

locationID	locationName
10001	Location1
10002	Location2
10003	Location3

and Items:

itemCode	locationID	qty
Item1	10001	10
Item1	10002	5
Item2	10001	7
Item2	10003	2

Now I just want one table with the information contained in it (for output to a qvd file). However, if I simply do this:

RIGHT JOIN (Items) LOAD
     locationID,
     locationName
RESIDENT Locations;
DROP FIELD locationID FROM Items;

I end up with this as my Items table:

itemCode	locationName	qty
Item1	Location1	10
Item1	Location2	5
Item2	Location1	7
Item2	Location3	2

However, what I want to end up with is an item entry for each valid Location, populated with a zero qty if none are listed in the imported table:

itemCode	locationName	qty
Item1	Location1	10
Item1	Location2	5
Item1	Location3	0
Item2	Location1	7
Item2	Location2	0
Item2	Location3	2

Any advice on how to code that in the script would be much appreciated. The source I'm reading will not output a zero qty line which would be the most obvious solution, but I'm just going to have to deal with that as I can't change it.

Thanks in advance,

Jay

[johnw]

I think this would work.

LEFT JOIN (Items)
LOAD
locationID as newLocationID
,locationName
RESIDENT Locations
;
NewItems:
LOAD
itemCode
,locationName
,if(locationID=newLocationID,qty,0) as qty
RESIDENT Items
;
DROP TABLES Items, Locations;

[#3 · Accepted solution]

Thanks John, that didn't completely give the right result but that was the kick in the right direction I needed!

One problem I still had was that each line was then creating a zero value entry so there was some duplicate lines; i.e. the table had item1, Location1, 0 and item1, Location1, 10...

Rather than add more to the if statement, grouping the new table worked just fine for me:

LEFT JOIN (Items)
LOAD
locationID as newLocationID
,locationName
RESIDENT Locations
;
NewItems:
LOAD
itemCode
,locationName
,Sum(if(locationID=newLocationID,qty,0)) as qty
RESIDENT Items
 
GROUP BY itemCode, locationName
;
DROP TABLES Items, Locations;

Thanks for the help