Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

anbu1984
Honored Contributor III

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

Load all fields and drop unnecessary fields as below

Opportunity:

LOAD     *;

SQL SELECT *

FROM Opportunity;


Drop fields field1,field2;

flipside
Valued Contributor II

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

Interesting query language SOQL!!

I've never used it myself, but a quick read seems to suggest the dot notation in fields is referring to a parent object (is that right?). If that is the case, are you able to return from each relevant table, with a linking key field between the tables and rebuild them in Qlikview.

I'm thinking in this case it would be three separate queries (in this case basing the link on id, but it could be something else) ...

SQL SELECT Id,

Name,

OwnerId

FROM Opportunity

SQL SELECT Id,

OwnerId

FROM Account

SQL SELECT Id,

OwnerId

FROM Broker_Account__r

Or does it not work that way?

flipside


Not applicable

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

That is EXACTLY what it's doing "." denotes a parent relationship and  "__r" denotes a custom relationship (this is similar to a join in normal SQL).

What I could do is create two mapping tables of the fields giving me issues, but I am trying my BEST not to do that and to stick to one table load since the load script itself is getting pretty big and I didn't want to do three DB calls when I could do one (the script in total does about 20 DB calls and a LOT of logic, so trying to keep is as simple as possible)

Not applicable

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

The fields I'm pulling will not show up if I do select * since those are relationship fields, this is similar to a join in SQL.

Highlighted
flipside
Valued Contributor II

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

Okay. If Qlikview doesn't like the dot notation, and I would try using as many variations of brackets and quotes as I could to see if it works, is it possible to build the select statement top down as this uses sub-queries and avoids dot notated fields.

www. sfdc99.com/2013/06/24/example-how-to-write-a-cross-object-soql-query-part-2/

flipside

Not applicable

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

Thanks, I'll experiment some more before I give up on this approach and split the query into simplified ones.

Not applicable

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

Wanted to update the thread in case someone was looking for an answer.

I created two Mapping tables with the joined fields and applied the map within the LOAD statement of the Opportunity table