Dots (.) in SOQL Load.. SOQL doesn't allow aliasing, the dilemma!

[#1]

So below is a simplified version of my query..

Opportunity:

LOAD     Id,

Name,

OwnerId,

Account.OwnerId,

Broker_Account__r.OwnerId;

SQL SELECT Id,

Name,

OwnerId,

Account.OwnerId,

Broker_Account__r.OwnerId

FROM Opportunity

The problem is:

• QlikView doesn't load "Account.OwnerID" and "Broker_Account__r.OwnerId" because it can't find the field (Script Error: Field not found - <Account.OwnerId>)
• SOQL doesn't allow for field Aliasing.

So, QV wants me to alias the field because it doesn't like dots, and SOQL won't let me alias and will include the dot!

Any idea on how I get around this?

[francoiscave]

Hi Fadi,

If you use the brackets ? or maybe the dobble quote ""

Like [Broker_Account__r.OwnerId]

François

[jjordaan]

Hi Fadi,

You can use the replace statement, like Replace(Replace([SQL Table Name],'.','_'),'/','_')

Hope this helps.

[#4]

Sadly, that didn't work.

[#5]

Could you elaborate on how to use the replace? If used in the Load statement it'll cause the same issue with the dot, no?

[jjordaan]

This is how I use it.

Tables:

LOAD * INLINE

[

    SQLTableName, SQLTableAlias, Active

  Item, Item, 1

];

Tables2BeLoaded:

LOAD

  Replace(Replace(SQLTableName,'.','_'),'/','_') AS SQLTable

  ,SQLTableAlias

  ,Incremental

  ,Active

Resident Tables

Where Active = 1;

And then start the loop over the tables with a FOR NEXT

[#7]

HI there,

You can try this -

Opportunity:

SQL SELECT Id,

Name,

OwnerId,

Account.OwnerId,

Broker_Account__r.OwnerId

FROM Opportunity

Store Opportunity into Opportunity.qvd(qvd);

Drop Table Opportunity;

Opportunity:

Load *

From Opportunity.qvd (qvd);

Now in the above qvd table you can do aliasing or any other qlikview function you want.

Thanks

[#8]

I attempted this as a first step:

Opportunity:

SQL SELECT Id,

Name,

OwnerId,

Account.OwnerId,

Broker_Account__r.OwnerId

FROM Opportunity

The result was pulling correctly for Id, Name and Owner Id. But it pulled the fields Account.OwnerId and Broker_Account__r.OwnerId as "Account" and "Broker_Account__r" and gave them all null values.

I verified that the SOQL itself works in salesforce, so I know it's not returning nulls there.

My guess is that it pulled data for Account.OwnerId and Broker_Account__r.OwnerId but wrote data in QV datamodel for "Account" and "Broker_Account__r" which don't exist, thus NULL...

[anbu1984]

Try this

Opportunity:

LOAD     Id,

Name,

OwnerId,

OwnerId_Account,

OwnerId_Broker_Account__r ;

SQL SELECT Id,

Name,

OwnerId,

Account.OwnerId As OwnerId_Account,

Broker_Account__r.OwnerId As OwnerId_Broker_Account__r

FROM Opportunity

[#10]

Sadly that did not work, SOQL doesn't allow aliasing.