topic Re: Adding hard coded date field (range) to an existing table in QlikView

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

Thu, 12 Nov 2015 14:03:31 GMT

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

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

settu_periasamy — Thu, 12 Nov 2015 15:15:54 GMT

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.

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

Thu, 12 Nov 2015 18:03:32 GMT

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!

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

MarcoWedel — Sun, 15 Nov 2015 22:46:47 GMT

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