topic Re: mapping data from two fields in QlikView

mapping data from two fields

Fri, 06 Sep 2013 06:41:04 GMT

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

Re: mapping data from two fields

Fri, 06 Sep 2013 07:06:22 GMT

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

Re: mapping data from two fields

Fri, 06 Sep 2013 07:07:38 GMT

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

Re: mapping data from two fields

Fri, 06 Sep 2013 07:08:17 GMT

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.

Re: mapping data from two fields

Fri, 06 Sep 2013 08:49:50 GMT

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.

Re: mapping data from two fields

tresB — Fri, 06 Sep 2013 08:54:14 GMT

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`;

Re: mapping data from two fields

Fri, 06 Sep 2013 09:16:58 GMT

I am getting field not found for

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

Re: mapping data from two fields

tresB — Fri, 06 Sep 2013 11:17:42 GMT

Hi Asmita,

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