Applymap Syntax?

[#1]

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!

[german_avanzato]

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

[Clever_Anjos]

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

or

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

[its_anandrjs]

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

[#5]

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

[#6]

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?

[its_anandrjs]

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

[Colin-Albert · Accepted solution]

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

;

[#9]

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