Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
Partner
Partner

data load editor - where to do an outer join clause

Hi - I have 3 columns from an excel brought into Qlik Sense. I am trying to do an outer join from the Lookup table to the Account table. How do I go about doing an outer join as I would in a sql database?

---- data load editor generated script ----

/* COLUMN 1 */

[Account]:
LOAD
[line_pid] AS [line_pid-lookup_id],
[account],
[amount],
If(amount>0,'Requestor',If(amount<0,'Source')) as Bucket,
If(amount<0,amount) as Source,
If(amount>0,amount) as Requestor,
[bucket],
[detailed_id] AS [account_id-detailed_id]
FROM [lib://Desktop/testingLineNameSyntax.xlsx]
(ooxml, embedded labels, table is Account);

 

/* COLUMN 2 */

[Detailed]:
LOAD
[year],
[account_id] AS [account_id-detailed_id],
[description]
FROM [lib://Desktop/testingLineNameSyntax.xlsx]
(ooxml, embedded labels, table is Detailed);

 

/* COLUMN3  */

[Lookup]:
LOAD
[lookup_id] AS [line_pid-lookup_id],
[value]
FROM [lib://Desktop/testingLineNameSyntax.xlsx]
(ooxml, embedded labels, table is Lookup);

Labels (2)
6 Replies
Channa
Valued Contributor III

Re: data load editor - where to do an outer join clause

by default qlik take full outer join if you want to do manually just add Outer join between tables

 

[Account]:
LOAD
[line_pid] AS [line_pid-lookup_id],
[account],
[amount],
If(amount>0,'Requestor',If(amount<0,'Source')) as Bucket,
If(amount<0,amount) as Source,
If(amount>0,amount) as Requestor,
[bucket],
[detailed_id] AS [account_id-detailed_id]
FROM [lib://Desktop/testingLineNameSyntax.xlsx]
(ooxml, embedded labels, table is Account);

 outer join 

/* COLUMN 2 */

[Detailed]:
LOAD
[year],
[account_id] AS [account_id-detailed_id],
[description]
FROM [lib://Desktop/testingLineNameSyntax.xlsx]
(ooxml, embedded labels, table is Detailed);

Channa
Partner
Partner

Re: data load editor - where to do an outer join clause

After validating the data in my sql database it doesn't seem like they are taking into account so I want to try this manually. My syntax in sql is:

from account a
inner join detailed b
on a.account_id = b.detailed_id
left outer join lookupTable c
on a.account_id = c.lookup_id

Given my syntax, would I put the "outer join" clause above /* COLUMN 3 */ or /* COLUMN 1 */ since this is where I am trying to do a left outer join? Apologize in advance if this seems rudimentary. Trying to get a hold on Qliks syntax.

Channa
Valued Contributor III

Re: data load editor - where to do an outer join clause

HI it will not do one column Left and one column Inner join

 

this case you directly write SQL in you DB Build View and call that view or just put ur SQL in Script, it will work

 

Channa
Partner
Partner

Re: data load editor - where to do an outer join clause

Hi, so I was able to do a left join successfully. Looks like all my rows are matching up right now after conducting a quick test in Qlik Sense. I'd like to ask - is there a way to do an incremental load. Lets say I just want to make a small change in the data load editor/script, looks like the process is always going the whole script vs just wanting to do just one small task.

Channa
Valued Contributor III

Re: data load editor - where to do an outer join clause

incremental load is possible 

u need key 

check attach for sample incremental load

Channa
Partner
Partner

Re: data load editor - where to do an outer join clause

Thanks for the documentation. Would this work for a simple calculation within a table and/or inserting a variable or something in that nature? Would I just use a "insert" syntax under the table I want to do this on? Only asking because the documentation refers to incremental loading for data and not necessarily computations. Thank you.