topic Re: ApplyMap function with imported SQL file only in QlikView

ApplyMap function with imported SQL file only

Tue, 03 Feb 2015 16:54:28 GMT

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 — Tue, 03 Feb 2015 18:08:36 GMT

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

maxgro — Tue, 03 Feb 2015 19:09:26 GMT

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

Wed, 04 Feb 2015 11:05:30 GMT

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

beanmachine — Mon, 31 Aug 2015 08:04:35 GMT

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.