4 Replies Latest reply: Nov 17, 2015 6:55 AM by Massimo Grossi RSS

    Load Script with Mapping-Load-Filter

    Christian Wylezol

      Hi,

       

      I hope someone can help me. I want to load from the Oracle Database.

      The normal LoadScript is working.

       

      My challenge is that I want to load only some data. For that I created a MappingLoad with 2 columns (repbasis, repbasis_used) . But the filter does not work. I get an error-message.

       

      oas_balance:
      LOAD
      CMPCODE  as cmpcode,
      EL1  as el1 ,
      REPBASIS  as repbasis,
      "FULL_VALUE"  as "full_value",

       

      ApplyMap('Map_Repbasis',REPBASIS) as  Repbasis_used
      ;
      SQL SELECT *
      FROM "OAS_BALANCE"
      WHERE CMPCODE LIKE '$(varCMPCODE)'
      //AND Repbasis_used = 'y' //not working
      AND YR >= '$(varMinJahrFix)'
      AND YR <= '$(varMaxJahrFix)' ;
      store oas_balance into $(vDataQVD)oas_balance.qvd;

       

      How can I load from the Database with a filter, that is not in the Database?

      I cannot list a repbasis entries, that I want to load (because this can Change).

       

      Thanks for your help in advance.

      Chris

        • Re: Load Script with Mapping-Load-Filter
          Mark Little

          Hi,

           

          The problem is table load from bottom to top,

          So the SQL runs, so the field you are mapping doesn't exist at that point.

           

          Maybe load the the repbasis you want first and load where exists?

           

          Mark

          • Re: Load Script with Mapping-Load-Filter
            Jonathan Dienst

            Repbasis_used does not exist in the database, so your cannot do the filtering like that. Either do the mapping and bring in Repbasis_used in the database with a SQL join, or you will have to bring in all the data in the SQL part and filter the LOAD part, like:

             

                 LOAD ...

                      ApplyMap('Map_Repbasis',REPBASIS) as  Repbasis_used

                 Where ApplyMap('Map_Repbasis',REPBASIS) = 'y'

                 ;

                 SQL SELECT ...

            • Re: Load Script with Mapping-Load-Filter
              Massimo Grossi

              you can filter at the Qlik side (pay attention that you get all rows from Oracle and the filter is in Qlik)

               

              LOAD
              CMPCODE  as cmpcode,
              EL1  as el1 ,
              REPBASIS  as repbasis,
              "FULL_VALUE"  as "full_value",

              ApplyMap('Map_Repbasis',REPBASIS) as  Repbasis_used

              where ApplyMap('Map_Repbasis',REPBASIS) = 'y'

              ;

              SQL select ....


               

              or you have few values to filter you can set the filter in a Qlik variable (var, usually using concat)

              and use that variable in Oracle


              where Repbabis_used in ('$(var'))