Changing a field at load based on another field

[Anonymous]

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

(ooxml, embedded labels, table is Query);

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

[daveamz]

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