Limit load through ApplyMap

[#1]

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

[maxgro]

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]

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.

[tresesco · Accepted solution]

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

[jonathandienst]

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.

[#6]

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.

[maxgro]

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?

[#8]

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