topic applymap...help plssss in QlikView

applymap...help plssss

Tue, 16 Jun 2015 09:52:54 GMT

Hi Experts,

i have the following script.

MapTable:

Mapping LOAD * INLINE [

OLD, NEW

0831, 0201

0208, 0214

0210, 0212

];

Branch:

LOAD

Applymap('MapTable', BRCODE, BRCODE) as BRCODE,

BRNAME,

FROM

QVD\BRANCH.qvd

(qvd);

Calls:

LOAD Applymap('MapTable', BRCODE, BRCODE) as BRCODE ,

Date,

BRNAME,

Answered

FROM

QVD\DATA.qvd

(qvd);

concatenate

LOAD Date,

BRNAME,

Lookup('BRCODE','BRNAME', BRNAME,'Calls') as BRCODE,

Answered

FROM

QVD\CallDetails.qvd

(qvd)

Sales:

LOAD Date,

Amount,

Applymap('MapTable', BRCODE, BRCODE) as BRCODE

FROM

QVD\Sales.qvd

(qvd);

I want to do mapping on BRCODE for all the above tables.

the applymap does works on all tables except Calls table.

the Calls table (after concatenate) doesn't have BRCODE, so i have done a lookup with the other table.

Now, i have to use applymap for Calls table (after concatenate).

How can i do this...pls help....

Re: applymap...help plssss

Gysbert_Wassenaar — Tue, 16 Jun 2015 10:04:35 GMT

Try this:

concatenate

LOAD Date,

BRNAME,

applymap('MapTable',Lookup('BRCODE','BRNAME', BRNAME,'Calls')) as BRCODE,

Answered

FROM

QVD\CallDetails.qvd

(qvd)

Re: applymap...help plssss

hic — Tue, 16 Jun 2015 10:05:42 GMT

You can either pipe your Lookup() into an Applymap(), like:

Concatenate

LOAD

Applymap('MapTable', BRCODE, BRCODE) as BRCODE,

Date,

BRNAME,

Answered;

LOAD

Date,

BRNAME,

Lookup('BRCODE','BRNAME', BRNAME,'Calls') as BRCODE,

Answered

FROM QVD\CallDetails.qvd (qvd)

Or you can skip all your Applymap() calls and instead use

Map BRCODE Using MapTable;

right after you load the mapping table.

HIC

Re: applymap...help plssss

Tue, 16 Jun 2015 10:12:40 GMT

Gysbert,

Thanks for the response.

but still its not working with calls table

Re: applymap...help plssss

Tue, 16 Jun 2015 10:23:29 GMT

thanks henric,

i tried like this,

MapTable:

Mapping LOAD * INLINE [

OLD, NEW

0831, 0201

0208, 0214

0210, 0212

];

MAP BRCODE Using MapTable;

Branch:

LOAD

BRCODE ,

BRNAME

FROM

QVD\BRANCH.qvd

(qvd);

Calls:

LOAD BRCODE,

Date,

BRNAME

Answered

FROM

QVD\DATA.qvd

(qvd);

concatenate

LOAD Date,

BRNAME,

Lookup('BRCODE','BRNAME', BRNAME,'Calls') as BRCODE,

Answered

FROM

QVD\CallDetails.qvd

(qvd);

Sales:

LOAD Date,

Amount,

ORDTYPE,

BRCODE

FROM

QVD\Sales.qvd

(qvd);

but still mapping is not done.

Can you pls help me with the script

Re: applymap...help plssss

hic — Tue, 16 Jun 2015 10:37:17 GMT

Without the qvw, it is impossible to say what goes wrong. But I have a suspicion that it is something with the number formats of the values that makes them not getting recognized...

Try testing a mapping table like:

MapTable:

Mapping LOAD 'X' & OLD as OLD, NEW INLINE [

OLD, NEW

...

and the corresponding change when you create the BRCODE:

'X' & BRCODE as BRCODE,

Then you can better see the values of BRCODE and why they don't match.

HIC

Re: applymap...help plssss

Tue, 16 Jun 2015 10:52:35 GMT

Henric,

Should i try like this..?

MapTable:

Mapping LOAD 'X' & OLD as OLD, NEW INLINE [

OLD, NEW

0831, 0201

0208, 0214

0210, 0212

];

MAP 'X' & 'BRCODE' as BRCODE Using MapTable;

pls help

Re: applymap...help plssss

Tue, 16 Jun 2015 10:53:31 GMT

help me out plsss

Re: applymap...help plssss

hic — Tue, 16 Jun 2015 11:22:14 GMT

The Mapping Load looks OK.

The Map statement should not be changed. It should be like this:

MAP BRCODE Using MapTable;

The Load statements where you load BRCODE should be changed:

Load

...

'X' & BRCODE as BRCODE,

...

HIC

Re: applymap...help plssss

Tue, 16 Jun 2015 14:21:27 GMT

Henric,

i have done like this

MapTable:

Mapping LOAD 'X' & OLD as OLD, NEW INLINE [

OLD, NEW

0831, 0201

0208, 0214

0210, 0212

];

MAP BRCODE Using MapTable;

Branch:

LOAD 'X' & BRCODE as BRCODE ,

BRNAME

FROM

QVD\BRANCH.qvd

(qvd);

Calls:

LOAD 'X' & BRCODE as BRCODE,

Date,

BRNAME,

Answered,

Unanswered,

Outgoing

FROM

QVD\DATA.qvd

(qvd);

concatenate

LOAD Date,

BRNAME,

Lookup('BRCODE' ,'BRNAME', BRNAME,'Calls') as BRCODE,

Total,

Answered,

Unanswered,

[Unans Miss %],

Outgoing

FROM

QVD\CallDetails.qvd

(qvd)

Now all the BRCODE has changed to code with prefix X like X0021 which is not correct.

Normal applymap is working for all tables except the part highlighted in bold red.

pls help me with some solution for that part

Re: applymap...help plssss

Tue, 16 Jun 2015 14:26:14 GMT

anybody help plsss

Re: applymap...help plssss

hic — Tue, 16 Jun 2015 14:40:10 GMT

You need to change the definition of BRCODE also in this table. The test is to precede it with 'X' everywhere. You haven't done that in the concatenated part of the Calls table. You should use

'X' & Lookup('BRCODE','BRNAME', BRNAME,'Calls') as BRCODE,

Further, the 'X' is not a solution - it is just a tool that (hopefully) makes the problem visible.

HIC

Re: applymap...help plssss

Tue, 16 Jun 2015 14:58:16 GMT

no henric,

its not working

pls give some other suggestions for that concatenate part

Re: applymap...help plssss

hic — Tue, 16 Jun 2015 15:21:09 GMT

It's impossible to tell what's wrong without the data.

HIC