topic Re: Limit load through ApplyMap in QlikView

Limit load through ApplyMap

Wed, 16 Sep 2015 15:42:19 GMT

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

Re: Limit load through ApplyMap

maxgro — Thu, 17 Sep 2015 09:44:55 GMT

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

Re: Limit load through ApplyMap

Digvijay_Singh — Thu, 17 Sep 2015 09:50:43 GMT

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.

Re: Limit load through ApplyMap

tresB — Thu, 17 Sep 2015 09:53:42 GMT

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

Re: Limit load through ApplyMap

jonathandienst — Thu, 17 Sep 2015 09:55:38 GMT

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.

Re: Limit load through ApplyMap

Thu, 17 Sep 2015 12:18:57 GMT

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

Re: Limit load through ApplyMap

maxgro — Thu, 17 Sep 2015 12:34:01 GMT

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?

Re: Limit load through ApplyMap

Thu, 17 Sep 2015 13:37:21 GMT

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