topic Re: OuterJoin inline in QlikView

OuterJoin inline

danosoft — Wed, 28 Nov 2018 16:15:39 GMT

Hi, i have this problem, i have 3 table inline populated in this way:

Tableuno:
LOAD * INLINE [
    A,B
    1, aa
    2, ss
    3, ee
];
Tabledue:
outer join (Tableuno)
LOAD * INLINE [
    A,C
    1, xx
    4, yy
];
Tabletre:
outer join (Tableuno)
LOAD * INLINE [
    A,C,D
    2, cc,dd
    6, ee,ff
];

When i show in a table i see for example 2 rows:

A,B,C,D

2,cc,-,-

2,-,cc,dd

But i want have for result in the table only 1 row in this way:

A,B,C,D

2,ss,cc,dd

is possible?

Thanks

Re: OuterJoin inline

Colin-Albert — Wed, 28 Nov 2018 17:30:00 GMT

~~You can do this with a INNER join~~

inner join (Tableuno)
~~LOAD * INLINE [~~
    ~~A,C,D~~
    ~~2, cc,dd~~
    ~~6, ee,ff~~
];

Ignore this is wrong!

https://help.qlik.com/en-US/qlikview/November2017/Subsystems/Client/Content/Scripting/combine-tables-join-keep.htm

Re: OuterJoin inline

danosoft — Wed, 28 Nov 2018 17:05:00 GMT

sorry but no... i want take all position like i did with the OUTER JOIN, only i want the result is in the same row, like i wrote

Re: OuterJoin inline

Colin-Albert — Wed, 28 Nov 2018 17:35:33 GMT

I would use applymap() rather than joins. The mapping tables in the example below have a data in a slightly dfferent sequence to your original loads, but give the answer you are looking for,

C_Map:
mapping LOAD * INLINE [
A,C
1, xx
4, yy
2, cc
6, ee
];

D_Map:
mapping LOAD * INLINE [
A,D
2, dd
6, ff
];

Tableuno:
load *
where C > '' and D > '' ;
LOAD
A,
B,
APPLYMAP('C_Map', A, '') AS C ,
APPLYMAP('D_Map', A, '') AS D
INLINE [
A, B
1, aa
2, ss
3, ee
];

This is about thinking of your data in QlikView terms and not as a database!
Here are a couple of useful blogs on the applymap function.

https://community.qlik.com/t5/Qlik-Design-Blog/Don-t-join-use-Applymap-instead/ba-p/1467592

https://www.quickintelligence.co.uk/applymap-is-it-so-wrong/

Re: OuterJoin inline

danosoft — Thu, 29 Nov 2018 08:02:05 GMT

Thanks, you it come the right row.... but.... the others rows.... was deleted, i can't see more in the table....