topic Re: Cartesian Product in QlikView

Cartesian Product

s2016 — Wed, 07 Jun 2017 07:46:25 GMT

Hi,

I have two tables like below. I want to join these two tables to get a cartesian product.

Such that CG c1 has

5 rows for e1 and

2 rows for e2 and

1 row for e3 and

so on and so forth for c2, c3, c4.

I thought I could achieve this using outer join but its only giving the result for c1 and rest of the CG rows are ignored.

Table1:

LOAD * INLINE [

CG, Entity

c1, e1

c1, e2

c1, e3

c2, e1

c2, e3

c3, e2

c3, e3

c4, e10

];

Table2:

LOAD * INLINE [

Entity, timeslot, data

e1, ts1, xxx1

e1, ts2, xxx2

e1, ts3, xxx3

e1, ts4, xxx4

e1, ts5, xxx5

e2, ts1, yyy1

e2, ts2, yyy2

e3, ts1, zzz1

e6, ts1, aaa1

];

Re: Cartesian Product

MK_QSL — Wed, 07 Jun 2017 08:11:41 GMT

What is the final output you are looking for?

Can you provide it here?

Re: Cartesian Product

antoniotiman — Wed, 07 Jun 2017 08:24:20 GMT

Hi,

try Inner Join

Regards,

Antonio

Re: Cartesian Product

Siva_Sankar — Wed, 07 Jun 2017 08:34:42 GMT

Hi Shashank.

Find the attached example.

-Siva

Re: Cartesian Product

s2016 — Thu, 08 Jun 2017 02:59:36 GMT

Thanks Everyone. I figured out later that the "Join" works fine on my test data and I am getting the results as expected in the above example dataset that I shared.

However, in my original table1, I have an additional CG_ID field which I auto generate in the previous layer using

'CG' & num(AutoNumber(CG_Name, 'auto1'), '000000000') as CG_ID

I noticed that if I removed the CG_ID field from my table1, the join would work perfectly. Otherwise, I would get no matching rows.

I am wondering why that might happen?

Re: Cartesian Product

s2016 — Thu, 08 Jun 2017 03:06:29 GMT

Hi Manish, I was expecting a result I would get with a join. My join is behaving differently somehow, because of an additional CGID field about which I have explained in the comment below.