I am trying to bring in some data into SPICE to be used in another dataset. My issue is that the data then doesn’t join properly when in SPICE, but if I load everything together, it works.
Here my set-up.
Big Daily Activity table containing a foreign key with a Product ID, type Integer.
Small Product table whose primary key is the Product ID, again type Integer.
Now, if I create my dataset adding the big Daily Activity table as main, then left joining the Product table on the product ID, and loading everything into SPICE, everything works as expected.
But if instead I create a SPICE dataset with the Daily Activity table only, save and load it, and then use it to create a second SPICE dataset where I left join the Product table on the product ID in the exact same way as above, then some IDs do not get joined, as if they had no match and as such the fields result in NULLs.
In other words in the first example I’d have Daily_Activity.FK_ProductId 123 matching Product.PK_ProductID 123, while in the second case Product.PK_ProductID for that row would be NULL.
I did this twice, without joining any other tables, and I always had the same results. I double checked, the type is always Integer and the values look fine, I also made sure to check the same IDs, to confirm that in one case they would work as expected and in the other they do not.
I googled, searched in the older posts (a bit difficult since the lack of proper keywords), and I checked the official documents, but I couldn’t find anything; my case is not in the “Known limitations” list as far as I can see and my situation seems a quite common one (using a dataset in another and join by some key). I am sorry if this was already asked or if the solution is trivial.
Any idea why this is happening, by any chance?
Just to clarify, I’d like to follow the dataset-in-another-dataset approach so that the bigger table can be furst loaded incrementally, and then everything gets a full refresh; as I can see it, this would require more space but the aim is to make it faster and less taxing on the DB.
Thank you in advance for your help.