Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Limit load through ApplyMap

Hi,

I am using mapping through ApplyMap to assign project groups to project IDs. Example is below

ApplyMap('PROJECTTAB',PROJECT_ID) as PROJECT_GROUP

The result is:

Project_ID,     Project_Group

1,                    A

2,                    A             

3,                    B   

4,                    B        

5,                    C

Now I want to limit Load only to projects from groups A and C.

I used following command   Where PROJECT_GROUP_DESC IN ('A','C')  but I got error saying

SQL##f - SqlState: S0022, ErrorCode: 904, ErrorMsg: [Oracle][ODBC][Ora]ORA-00904: "PROJECT_GROUP": invalid identifier

SQL SELECT *

FROM "BUDGET"

Where PROJECT_GROUP_DESC IN ('A','C')

When I change it to Project_ID instead of Project_Group it works fine. But I don't want to list all the projects.

Any idea how to solve this problem ?

Thanks a lot.

Lukas

1 Solution

Accepted Solutions
MVP
MVP

Re: Limit load through ApplyMap

Applymap is applied in qv after data gets loaded from source. Therefore, you can't use mapped field (newly created in qv) during load from source, because, then that field doesn't exist at all. So if you try to restrict during load from source, you can rather compare with source field Project_ID, like:

SQL SELECT *

FROM "BUDGET"

Where Project_ID IN ('1','2', '5');

Otherwise, you might have to use a preceding load and apply the condition like:

Table:

Load

     *

Where Match(PROJECT_GROUP_DESC, 'A','C');

Load

          *

          ApplyMap('PROJECTTAB',PROJECT_ID) as PROJECT_GROUP;

SQL SELECT *

FROM "BUDGET";

7 Replies
MVP
MVP

Re: Limit load through ApplyMap

here you're filtering (with where) in the Oracle database; do you have a field PROJECT_GROUP_DESC in Oracle db?

SQL SELECT *

FROM "BUDGET"

Where PROJECT_GROUP_DESC IN ('A','C')



maybe with (filter in QlikView, all records loaded from Oracle)


load *

where match(ApplyMap('PROJECTTAB',PROJECT_ID), 'A', 'C');

SQL SELECT *

FROM "BUDGET";


Digvijay_Singh
Honored Contributor III

Re: Limit load through ApplyMap

Can you clarify how you are using PROJECT_GROUP_DESC  and  PROJECT_GROUP-

Your statement below used DESC but error message is showing PROJECT_GROUP

I used following command   Where PROJECT_GROUP_DESC IN ('A','C')  but I got error saying.

Also while loading from source, you cannot use newly created field in first load script, as only fields available in source can be used in query string. You may need preceding load or resident load to apply more filter.

MVP
MVP

Re: Limit load through ApplyMap

Applymap is applied in qv after data gets loaded from source. Therefore, you can't use mapped field (newly created in qv) during load from source, because, then that field doesn't exist at all. So if you try to restrict during load from source, you can rather compare with source field Project_ID, like:

SQL SELECT *

FROM "BUDGET"

Where Project_ID IN ('1','2', '5');

Otherwise, you might have to use a preceding load and apply the condition like:

Table:

Load

     *

Where Match(PROJECT_GROUP_DESC, 'A','C');

Load

          *

          ApplyMap('PROJECTTAB',PROJECT_ID) as PROJECT_GROUP;

SQL SELECT *

FROM "BUDGET";

MVP
MVP

Re: Limit load through ApplyMap

The SQL message suggests that you are attempting to PROJECT_GROUP in the SQL script and that field does not exist in the SQL source table/view.

This does not match the script fragment you posted, so its a hard to draw conclusions. I suggest that you post more of your script, including where you generate the mapping table and the complete load statetement that is generating the error.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable

Re: Limit load through ApplyMap

I am sorry for confusion. PROJECT_GROUP_DESC and PROJECT_GROUP are the same thing. I use PROJECT_GROUP_DESC in the script but I just simplified it in the example to PROJECT_GROUP. But I forgot to change in the Where statement. So this is not the problem.

I tried what maxgro suggested (where match(ApplyMap('PROJECTTAB',PROJECT_ID), 'A', 'C')Smiley Wink but it didn't work. I got the same error message.

However, preceding load is exactly what I need. I am gonna try it and hope for the best.

Thanks guys.

MVP
MVP

Re: Limit load through ApplyMap

1) you can't get the same error (a error from the database)

SQL##f - SqlState: S0022, ErrorCode: 904, ErrorMsg: [Oracle][ODBC][Ora]ORA-00904: "PROJECT_GROUP": invalid identifier

because the sql part I posted doesn't have  PROJECT_GROUP

SQL SELECT *

FROM "BUDGET";



2) also I think if this works (Tresesco)

Table:

Load

    *

Where Match(PROJECT_GROUP_DESC, 'A','C');

Load

          *

          ApplyMap('PROJECTTAB',PROJECT_ID) as PROJECT_GROUP;

SQL SELECT *

FROM "BUDGET";



this should works too


load *

where match(ApplyMap('PROJECTTAB',PROJECT_ID), 'A', 'C');

SQL SELECT *

FROM "BUDGET";


It seems to me the first one is just a split in two steps of the second (do we need 2 steps instead of 1?)

Or maybe I'm missing something?


Not applicable

Re: Limit load through ApplyMap

The actual error is:
SQL##f - SqlState: S1000, ErrorCode: 920, ErrorMsg: [Oracle][ODBC][Ora]ORA-00920: invalid relational operator

SQL SELECT *

FROM "BUDGET"

Where match(ApplyMap('PROJECTTAB',PROJECT_ID),'A')

You are right, I see the difference now. It was error 904 before and not it error 920.

And I just realized where is the problem.I had to move the where clause before SQL SELECT - according to your first message. Now it is working like a charm   I feel stupid for not seeing it before.

Thanks a lot

Community Browser