mapping data from two fields

[#1]

I have data as follows:

Block No	Occ Time	Actual Occ Time
4	5/8/2013 3:07:00 PM	5/8/2013  3:15:00 PM
5	5/8/2013  3:10:00 PM	5/8/2013  3:17:00 PM
7	5/8/2013  3:15:00 PM	5/8/2013 3:07:00 PM
8	5/8/2013  3.17.00 PM	5/8/2013  3.10.00 PM

I get Block No and Occ Time from Database whereas I calculate Actual Occ Time.

I have to link corresponding block no i.e 5/8/2013 3:15:00 PM  in Actual Occ Time should correspond to Block No 7.

How should I do this mapping?

Thanks

Asmita

[#2]

I guess you are calculating Actual OCC on script itself and on different table.

So one simple way using Apply map function you get block no on second table and it will create link automatically.

Karthik

[#3]

I am calculating Actual OCC on same table.So there won't be automatic mapping

[#4]

Hi,

First, prepare a Mapping with both, Block No and Occ Time:

'Map_Time':

Mapping Load

     Num( [Occ Time])

     [Block No]

From YourTable;

And then, when you have calculated de Actual Occ Time, do the Mapping:

...

ApplyMap('Map_Time', Num([Actual Occ Time]), 'Without coincidence') as [Block No]

...

Remenber to convert Occ Time and Actual Occ Time to numeric for the mapping, so QV will perform the mapping better.

[#5]

I have written as follows:

Map_Time:

Mapping Load

     Num([OCC_DATETIME]),

     [BLOCK_NO]

From `abc_site`.`tamper_events`;

LOAD REC_DATETIME as Tamper_REC_DATETIME,

    `TAMPER_DUR`as Tamper_TAMPER_DUR,

    timestamp(`REC_DATETIME`-Time#(TAMPER_DUR,'mm')) as TimeInterval,

    ApplyMap('Map_Time',Num([timestamp(`REC_DATETIME`-Time#(TAMPER_DUR,'mm'))])) as [BLOCK_NO] ;

SQL SELECT `REC_DATETIME`,

   `TAMPER_DUR`

FROM `abc_site`.`tamper_events`;

I am getting an error stating cannot open abc_site.

[tresesco]

Try this:

Map_Time:

Mapping Load

     Num([OCC_DATETIME]),

     [BLOCK_NO];

SQL SELECT `OCC_DATETIME`,

   `BLOCK_NO`

FROM `abc_site`.`tamper_events`;

LOAD REC_DATETIME as Tamper_REC_DATETIME,

    `TAMPER_DUR`as Tamper_TAMPER_DUR,

    timestamp(`REC_DATETIME`-Time#(TAMPER_DUR,'mm')) as TimeInterval,

    ApplyMap('Map_Time',Num([timestamp(`REC_DATETIME`-Time#(TAMPER_DUR,'mm'))])) as [BLOCK_NO] ;

SQL SELECT `REC_DATETIME`,

   `TAMPER_DUR`

FROM `abc_site`.`tamper_events`;

[#7]

I am getting field not found for

Num([timestamp(`REC_DATETIME`-Time#(TAMPER_DUR,'mm'))]))

[tresesco]

Hi Asmita,

Remove all the single quotes around the field names under LOAD section(fine when under SQL).