LOAD DATA THAT LIES BETWEEN bushyhours TIMESTART AND TIMEEND

[aj0031724]

Team,

I need to load only those data from master table(CONTAING 24HOURS DATA) that falls between the timerange from another dimension table group by each RNC & DATE.

for eample:

RNCTABLE:

RNC   DATE    TIMESTART    TIMEEND

R1    01/08/2014  06:00              13:00

R2     02/08/2014     09:00          16:00

MASTERTABLE:

   RNC                   TIMESTAMP                      VALUE1

R1	01/08/14	10
R1	01/08/14 01:00	11
R1	01/08/14 02:00	12
R1	01/08/14 03:00	13
R1	01/08/14 04:00	14
R1	01/08/14 05:00	15
R1	01/08/14 06:00	16
R1	01/08/14 07:00	17
R1	01/08/14 08:00	18
R1	01/08/14 09:00	19
R1	01/08/14 10:00	20
R1	01/08/14 11:00	21
R1	01/08/14 12:00	22
R1	01/08/14 13:00	23
R1	01/08/14 14:00	24
R1	01/08/14 15:00	25
R1	01/08/14 16:00	26
R1	01/08/14 17:00	27
R1	01/08/14 18:00	28
R1	01/08/14 19:00	29
R1	01/08/14 20:00	30
R1	01/08/14 21:00	31
R1	01/08/14 22:00	32
R1	01/08/14 23:00	33
R1	02/08/14 00:00	34
R2	02/08/24 01:00	10
R2	02/08/24 02:00	20
R2	02/08/24 03:00	30
R2	02/08/24 04:00	40
R2	02/08/24 05:00	50
R2	02/08/24 06:00	60
R2	02/08/24 07:00	70
R2	02/08/24 08:00	80
R2	02/08/24 09:00	90
R2	02/08/24 10:00	100
R2	02/08/24 11:00	110
R2	02/08/24 12:00	120
R2	02/08/24 13:00	130
R2	02/08/24 14:00	140
R2	02/08/24 15:00	150
R2	02/08/24 16:00	160
R2	02/08/24 17:00	170
R2	02/08/24 18:00	180
R2	02/08/24 19:00	190
R2	02/08/24 20:00	200
R2	02/08/24 21:00	210
R2	02/08/24 22:00	220
R2	02/08/24 23:00	230

OUTPUT WHICH WE WANT IS:

FINALTABLE:

   RNC                            TIMESTAMP               VALUE1

R1	01/08/14 06:00	16
R1	01/08/14 07:00	17
R1	01/08/14 08:00	18
R1	01/08/14 09:00	19
R1	01/08/14 10:00	20
R1	01/08/14 11:00	21
R1	01/08/14 12:00	22
R1	01/08/14 13:00	23
R2	02/08/24 09:00	90
R2	02/08/24 10:00	100
R2	02/08/24 11:00	110
R2	02/08/24 12:00	120
R2	02/08/24 13:00	130
R2	02/08/24 14:00	140
R2	02/08/24 15:00	150
R2	02/08/24 16:00	160

can anyone please help me and suggest how can I write a load script as per above.

[rubenmarin]

Hi Avinash, this script returns a table like that:

RNCTABLE:

LOAD RNC,

    Timestamp(Date(DATE) + Time(TIMESTART)) as Start,

    Timestamp(Date(DATE) + Time(TIMEEND)) as End

Inline [

RNC,DATE,TIMESTART,TIMEEND

R1,01/08/2014,06:00,13:00

R2,02/08/2014,09:00,16:00

];

MASTERTABLE:

LOAD RNC,

    Timestamp(Timestamp#(TIMESTAMP, 'DD/MM/YY hh:mm')) as DateTime,

    VALUE1

Inline [

RNC,TIMESTAMP,VALUE1

R1,01/08/14,10

R1,01/08/14 01:00,11

R1,01/08/14 02:00,12

R1,01/08/14 03:00,13

R1,01/08/14 04:00,14

R1,01/08/14 05:00,15

R1,01/08/14 06:00,16

R1,01/08/14 07:00,17

R1,01/08/14 08:00,18

R1,01/08/14 09:00,19

R1,01/08/14 10:00,20

R1,01/08/14 11:00,21

R1,01/08/14 12:00,22

R1,01/08/14 13:00,23

R1,01/08/14 14:00,24

R1,01/08/14 15:00,25

R1,01/08/14 16:00,26

R1,01/08/14 17:00,27

R1,01/08/14 18:00,28

R1,01/08/14 19:00,29

R1,01/08/14 20:00,30

R1,01/08/14 21:00,31

R1,01/08/14 22:00,32

R1,01/08/14 23:00,33

R1,02/08/14 00:00,34

R2,02/08/14 01:00,10

R2,02/08/14 02:00,20

R2,02/08/14 03:00,30

R2,02/08/14 04:00,40

R2,02/08/14 05:00,50

R2,02/08/14 06:00,60

R2,02/08/14 07:00,70

R2,02/08/14 08:00,80

R2,02/08/14 09:00,90

R2,02/08/14 10:00,100

R2,02/08/14 11:00,110

R2,02/08/14 12:00,120

R2,02/08/14 13:00,130

R2,02/08/14 14:00,140

R2,02/08/14 15:00,150

R2,02/08/14 16:00,160

R2,02/08/14 17:00,170

R2,02/08/14 18:00,180

R2,02/08/14 19:00,190

R2,02/08/14 20:00,200

R2,02/08/14 21:00,210

R2,02/08/14 22:00,220

R2,02/08/14 23:00,230

];

Inner Join IntervalMatch(DateTime, RNC) LOAD Start, End, RNC Resident RNCTABLE;

DROP Table RNCTABLE;

[malini_qlikview]

Hi,

You can use Interval match, attached is the working model of the same

[miguelbraga]

Hey,

Donwload the attached file Avinash Jain based on Ruben Solution.

Have a good job