topic Re: Apply Map Issue in QlikView

Apply Map Issue

coolwaters — Wed, 25 Nov 2020 16:16:04 GMT

HI,

I am using apply map in an applcation and am getting an error that cannot understand. Am re-creating the problem part below with part of the data.

There are two tables:

1) TRANS

2) AMT

Using applymap I need to get the sales value from the AMT table to the TRANS Table.

The error I get is:

I am using QlikView 11.20 Sr 17

Below is the code:

TRANS:
LOAD NAME,
      CODE,
      DATE,
      TRANS

FROM
[TRANS DATA.xls]
(biff, embedded labels, table is TRANS$);

AMT:
LOAD DATE,
      TRANS,
      TRANS &':'& FLOOR(DATE(DATE#(DATE,'MM/DD/YYYY'),'DD/MM/YYYY')) as KEY_TRANS,
      AMT_PAID
FROM
[TRANS DATA.xls]
(biff, embedded labels, table is AMT$);

AMT_MAP:
MAPPING LOAD
KEY_TRANS,
AMT_PAID
Resident AMT;
DROP TABLE AMT;

DATA:
LOAD
TRANS &':'& FLOOR(DATE(DATE#(DATE,'MM/DD/YYYY'),'DD/MM/YYYY')) AS KEY_TRANS,
*
RESIDENT TRANS;
DROP TABLE TRANS;

FINAL:
LOAD
ApplyMap('AMT_MAP',KEY_TRANS, 0) AS AMT_PAID,
*
RESIDENT DATA;
DROP TABLE DATA;

Re: Apply Map Issue

sunny_talwar — Sat, 25 Nov 2017 03:59:38 GMT

Not sure I understand the behavior, but this seems to solve the problem

TRANS:

LOAD NAME,

CODE,

DATE,

Text(TRANS) as TRANS

FROM

[TRANS DATA.xls]

(biff, embedded labels, table is TRANS$);

AMT:

LOAD DATE,

TRANS,

TRANS &' : '& FLOOR(DATE(DATE#(DATE,'MM/DD/YYYY'),'DD/MM/YYYY')) as KEY_TRANS,

AMT_PAID

FROM

[TRANS DATA.xls]

(biff, embedded labels, table is AMT$);

AMT_MAP:

MAPPING

LOAD KEY_TRANS,

AMT_PAID

Resident AMT;

DROP TABLE AMT;

DATA:

LOAD TRANS &' : '& FLOOR(DATE(DATE#(DATE,'MM/DD/YYYY'),'DD/MM/YYYY')) AS KEY_TRANS,

RESIDENT TRANS;

DROP TABLE TRANS;

FINAL:

LOAD ApplyMap('AMT_MAP',KEY_TRANS, 0) AS AMT_PAID,

RESIDENT DATA;

DROP TABLE DATA;

Re: Apply Map Issue

coolwaters — Sat, 25 Nov 2017 05:27:37 GMT

Hi Sunny,

Thanks as always for helping out, it works.

Appreciate it.

Re: Apply Map Issue

ysj — Fri, 19 Jan 2018 06:28:02 GMT