Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a dataset where there is an identifier called entityGUID, there is also a granularity identifier in the data set, so if the granularity is house, the data refers to a house within a complex, but if the identifier is called entity it is referring to the complex. I have other joining tables that contain further information on each house and complex. When I load my data I want to set a separate identifier (house GUID or Complex GUID) or table for the house or complex information.
I just can't see how to do this - sorry very new - could someone point me in the right direction. The file format looks like this:
Summary:
LOAD
EntityGUID,
Granularity,
FirstDatePlaced,
AvgDatePlaced,
FirstDateSold,
LastDateSold,
AvgDateSold,
DateMovedOut,
DateMovedIn,
FROM
(
I have tried a simple if statement and a resident load using if but nothing seems to work how I think it should.
if(Granularity='House')then
HouseSummary:
LOAD
EntityGUID as FFHGUID,
FirstDatePlaced,
AvgDatePlaced,
FirstDateSold,
LastDateSold,
Resident Summary;
if(Granularity='Entity')then
FarmSummary:
LOAD
EntityGUID as FFGUID,
FirstDatePlaced,
AvgDatePlaced,
FirstDateSold,
LastDateSold,
Resident Summary,
DROP Summary;
I really appreciate any help that someone could give.
Many thanks
Hi Dominic,
To achieve that you can:
1. You can keep everything in one table and create a a key based on the granularity field
Summary:
LOAD
Granularity & '_' & EntityGUID AS %ENTITY_KEY
....
FROM
....
To link the other tables (e.g. Entity Details) you must create a similar key, based on the source of the data:
[House Details]:
LOAD 'House_' & ID AS %ENTITY_KEY
...
FROM House Details;
Or, something similar (I don't know how your data model looks).
2. You can split your summary.xls data set into 2 different tables and name the key field differently:
HouseSummary:
LOAD
EntityGUID as HOUSE_KEY,
FirstDatePlaced,
AvgDatePlaced,
FirstDateSold,
LastDateSold,
Resident Summary
WHERE Granularity = 'House';
EntitySummary:
LOAD
EntityGUID as ENTITY_KEY,
FirstDatePlaced,
AvgDatePlaced,
FirstDateSold,
LastDateSold,
Resident Summary
WHERE Granularity = 'Entity';
But you will have to rename the rest of the fields to avoid creation of synthetic keys (e.g. FirstDatePlaced AS HouseFirstDatePlaced)
Regards,
David