topic Re: Applymap Syntax? in QlikView

Applymap Syntax?

Fri, 30 Jan 2015 14:05:04 GMT

Hi, I am trying to use ApplyMap, but I'm having syntax issues:

RenewMap:

Mapping LOAD

OP.Accountno,

Count(OP.ProductName) As RenewProduct;

SQL Select

OP.ACCOUNTNO,

OP.PRODUCTNAME

from

Goldmine_Sales..OpMgr OP

WHERE

LEFT(OP.STAGE,1)='R';

The error is "PRODUCTNAME" not found, even though the field exists.

Questions: Can I use an aggregation function like 'count' in the ApplyMap? If so, does the syntax change?

Thanks!

Re: Applymap Syntax?

german_avanzato — Fri, 30 Jan 2015 14:09:49 GMT

Maybe is because you use OP.PRODUCTNAME and later use OP.ProductName, this two field are different.

Re: Applymap Syntax?

Clever_Anjos — Fri, 30 Jan 2015 14:12:47 GMT

RenewMap:

Mapping LOAD

OP.Accountno,

Count(OP.ProductName) As RenewProduct

GROUP BY OP.Accountno;

SQL Select

OP.ACCOUNTNO,

OP.PRODUCTNAME

from

Goldmine_Sales..OpMgr OP

WHERE

LEFT(OP.STAGE,1)='R';

RenewMap:

Mapping LOAD

OP.Accountno,

RenewProduct;

SQL Select

OP.ACCOUNTNO,

count(OP.PRODUCTNAME) as RenewProduct

from

Goldmine_Sales..OpMgr OP

WHERE

LEFT(OP.STAGE,1)='R'

group by OP.ACCOUNTNO

Re: Applymap Syntax?

its_anandrjs — Fri, 30 Jan 2015 14:12:56 GMT

Hi,

When you use any agregation function like SUM Count you have to use Group by then try this

RenewMap:

Mapping LOAD

OP.Accountno,

Count(OP.ProductName) As RenewProduct

Group By OP.Accountno;

SQL Select

OP.ACCOUNTNO,

OP.PRODUCTNAME

from

Goldmine_Sales..OpMgr OP

WHERE

LEFT(OP.STAGE,1)='R';

Regards

Anand

Re: Applymap Syntax?

Fri, 30 Jan 2015 14:15:02 GMT

No that was just lazy typing; it's all CAPS in the script.

Re: Applymap Syntax?

Fri, 30 Jan 2015 14:29:04 GMT

I tried it both ways, with the Count and Group by in the Mapping Load, and then with the Count and Group By in the SQL select.

RenewMap:

MAPPING LOAD

OP.ACCOUNTNO,

Count(OP.PRODUCTNAME) As RenewProduct

GROUP BY OP.ACCOUNTNO;

SQL Select

OP.ACCOUNTNO,

OP.PRODUCTNAME

From

GoldMine_Sales_and_Marketing..OpMgr OP

WHERE

LEFT(OP.STAGE,1)='R';

This gives the error "Field not found: OP.ACCOUNTNO".

Then I tried:

RenewMap:

MAPPING LOAD

OP.ACCOUNTNO,

RenewProducts;

SQL Select

OP.ACCOUNTNO,

COUNT(OP.PRODUCTNAME) As RenewProducts

From

GoldMine_Sales_and_Marketing..OpMgr OP

WHERE

LEFT(OP.STAGE,1)='R'

GROUP BY

OP.ACCOUNTNO;

Same error: Field Not Found OP.ACCOUNTNO

Now, in my whole script, it looks like:

{Mapping Load code, as above}

{LOAD statement for the OpMgr table}

{SQL Select statement for the OpMgr table} <- trying to use ApplyMap in this code

Should the mapping load block be after the LOAD statement block? In it?

Re: Applymap Syntax?

its_anandrjs — Fri, 30 Jan 2015 15:10:06 GMT

Hi,

Check you are using the correct fields name or correctley use Group By or Where statements if yo are using it into SQL script then use Group by and where there otherwise in the qlikview load statement.

1. First way of doing

RenewMap:

MAPPING LOAD

OP.ACCOUNTNO,

Count(OP.PRODUCTNAME) As RenewProduct

GROUP BY OP.ACCOUNTNO

WHERE

LEFT(OP.STAGE,1)='R';

SQL Select

OP.ACCOUNTNO,

OP.PRODUCTNAME

From

GoldMine_Sales_and_Marketing..OpMgr OP;

2. Second way of doing is

RenewMap:

MAPPING LOAD

OP.ACCOUNTNO,

RenewProducts;

SQL Select

OP.ACCOUNTNO,

COUNT(OP.PRODUCTNAME) As RenewProducts

From

GoldMine_Sales_and_Marketing..OpMgr OP

WHERE

LEFT(OP.STAGE,1)='R'

GROUP BY

OP.ACCOUNTNO;

Better you try first on on that you have no confusion on field names

Regards

Anand

Re: Applymap Syntax?

Colin-Albert — Fri, 30 Jan 2015 15:16:40 GMT

Your query should be

RenewMap:

Mapping LOAD

ACCOUNTNO,

RENEWPRODUCT;

SQL Select

OP.ACCOUNTNO,

COUNT(OP.PRODUCTNAME) AS RENEWPRODUCT

from

Goldmine_Sales..OpMgr OP

WHERE

LEFT(OP.STAGE,1)='R'

GROUP BY OP.ACCOUNTNO

;

Re: Applymap Syntax?

Fri, 30 Jan 2015 15:41:51 GMT

Thanks, Colin. I didn't realize that having the table alias in the Mapping Load was causing the problem.