Adding hard coded date field (range) to an existing table

[#1]

I searched but couldn't find this example. I need to add a new field to an existing table that is simply a date range -- specifically, I need to add the ability to filter on "Compensation Period" ranges. I added a small dataset below. I just want to inject this field into an already existing table. Best suggestion to do this??

Field = Compensation Period

Ex. values (note: these values are defined values):

1/2/14 - 5/30/14

5/31/14 - 12/31/14

1/1/15 - 5/28/15

5/29/15 - 12/30/15

[settu_periasamy]

Hi Andrew,

The below is based on my understanding. If you have the date field in your existing field, you can try to create the newfield using IntervalMatch. Like

Compensation:
LOAD * INLINE [
Start,End
1/2/14,5/30/14
5/31/14,12/31/14
1/1/15,5/28/15
5/29/15,12/30/15
];
Date_Table:
LOAD * INLINE [
Date
6/12/14,
12/31/14
1/11/15,
5/10/15
5/29/15
];


NoConcatenate
T1:
IntervalMatch (Date) LOAD Start,End Resident Compensation;


New:
LOAD *,Start&' - '&End  as Compensation_Period Resident T1;
DROP Table Compensation,Date_Table,T1;

Let us know, if this not your requirement.

[#3]

Thanks for the detailed response. I believe my requirement is simpler than this. I just need to "synthetically" create a new field that I can then filter on.

Datasource1:

Many existing fields

Create a new field = Compensation Periods

Values: as originally supplied (i.e a range of dates, such as, 1/2/15 - 5/29/15)

Note: the date ranges do NOT specifically match any existing dates in the data model.

Any additional thoughts would be much appreciated!

[MarcoWedel]

Hi,

one solution to implement your specified requirements:

Datasource1:
LOAD Rand() as field1,
    Rand() as field2,
    Rand() as fieldn
AutoGenerate 10;

Join

LOAD * Inline [
Compensation Period
1/2/14 - 5/30/14
5/31/14 - 12/31/14
1/1/15 - 5/28/15
5/29/15 - 12/30/15
];

hope this helps

regards

Marco