4 Replies Latest reply: Aug 31, 2015 4:04 AM by Richard de Gruchy RSS

    ApplyMap function with imported SQL file only

      Hi all - I'm having qlik problems with a common field between several tables and naturally I googled the issue as it seemed counter-intuitive for my having to write a join myself - and found articles similar to these: Don't join - use Applymap instead

       

      Which are excellent! Except I'm unsure of the correct syntax since I've imported an sql file and the tutorials I've seen does not account for that.

       

      What I have in qlik is a table structure as: http://i.imgur.com/pKTMhuw.png

      What I need is for the user to be linked to the company, like so: http://i.imgur.com/YT8bO9T.png

       

      However since the user is a common field, qlik is calling some ambiguous loop error and causing me issues when I try connect user to company (and activity).

       

      With Applymap I've done:

       

      MapUserNametoCompany:

      Mapping Load UserID, UserName;

      select ID as UserID, Name as UserName

      From User;

       

      Company:

      Load *,

      ApplyMap('MapUserNametoCompany', UserID, null()) as UserName;

      SELECT ID as CompanyID, CountryID, Name as CompanyName

      From Company;

       

      But of course it complains that there is no common field of UserID in the Company table (which is the whole reason I'm looking for an alternative to a join in the first place), which I understand, but am I'm a little confused as to how to apply ApplyMap now.

       

      In these situations how would you handle this? If ApplyMap is the solution could you please explain to me how (without attaching files as I'm using the trial version for my first project) and I would be eternally grateful.

       

      Kind regards,

       

      raz

        • Re: ApplyMap function with imported SQL file only
          Clever Anjos

          Your statement

          Company:

          Load *,

          ApplyMap('MapUserNametoCompany', UserID, null()) as UserName;

          SELECT ID as CompanyID, CountryID, Name as CompanyName

          From Company;

           

          does not work, since you are not retrieving an UserID from Company table, UserID should be in your select clause

          • Re: ApplyMap function with imported SQL file only
            Massimo Grossi

            I think you have a table User with CompanyID and you want to add the CompanyName to the UserID

             

            MapCompany:

            Load CompanyID, CompanyName;

            SELECT ID as CompanyID, Name as CompanyName

            From Company;

             

            User:

            Load

                 UserID, UserName,

                 // add company name using company id

                 applymap('MapCompany', ???companyid???) as UserCompanyName

            ;

            select ID as UserID, Name as UserName, ???companyid???

            From User;

            • Re: ApplyMap function with imported SQL file only

              I realized the issue was with the infinite loop but I wanted to apply my applymap knowledge elsewhere:

               

              MapExchangeRate:

              Load CurrencyID, ExchangeRate;

              Select ID as CurrencyID, ExchangeRate

              From Currency;

               

              FlowItem:

              Load *,

              ApplyMap('MapExchangeRate', CurrencyID) as CurrencyExchange;

              SELECT FlowID, CurrencyID, ProductID, Price as FlowItemPrice, TargetGroupID, CampaignStart as FlowItemCampaignStart, CampaignStop as FlowItemCampaignStop

              From FlowItem;

               

              This doesn't return any 'compile' errors on another duo of tables but the "CurrencyExchange" is always empty when I add it to a table (which should never be the case as the fields are not nullable).

               

              Screenie of my qlik structure looks correct as well: http://i.imgur.com/VIRiFoH.png

               

              I also want to manipulate the translated field IE FlowItemPrice * ExchangeRate (as introduced into the table) but I'm unsure how.

                • Re: ApplyMap function with imported SQL file only
                  Richard de Gruchy

                  You need to specify your mapping table as such, therefore, the code should be:

                   

                  MapExchangeRate:

                  Mapping Load CurrencyID, ExchangeRate;

                  Select ID as CurrencyID, ExchangeRate

                  From Currency;

                   

                  In your example, there is no error when the script is run even though the MapExchangeRate table is not a mapping table.  QlikView just ignores errors with ApplyMap and populates the field with a null.