7 Replies Latest reply: Sep 17, 2015 9:37 AM by Lukas Formanek RSS

    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

        • Re: Limit load through ApplyMap
          Massimo Grossi

          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

            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
              Tresesco B

              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
                Jonathan Dienst

                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

                  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
                      Massimo Grossi

                      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

                          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