topic Re: How to create row for each record combination in two tables? in App Development

How to create row for each record combination in two tables?

rayzac99 — Fri, 26 Jan 2018 17:41:43 GMT

I have two tables. One is a list of dealers. The other is a list of products. I want to create one table that has a row for each dealer and each product. What is the best way to do this in Qlik Sense?

Table 1 (dealer)

Table 2 (products)

Result table

A 1

A 2

A 3

B 1

B 2

B 3

C 1

C 2

C 3

Thanks in advance!

Re: How to create row for each record combination in two tables?

Anonymous — Fri, 26 Jan 2018 19:21:10 GMT

¿How are the two tables relating to each other? ¿Which is the key?

¿Are you asking to do it by script or in the frontend?

Regards, Josefina

Re: How to create row for each record combination in two tables?

rayzac99 — Fri, 26 Jan 2018 19:23:02 GMT

They are not related now. I would like to do this by script to create a table so I can then relate it to other existing tables.

Re: How to create row for each record combination in two tables?

Anonymous — Fri, 26 Jan 2018 19:37:33 GMT

Load

...

From Table 1 (dealer)

Join

Load

...

From

Table 2 (product)

That would do a cross join. If you need to relate specific products to specific vendors you need to create a key field between the two tables.

Re: How to create row for each record combination in two tables?

pradosh_thakur — Fri, 26 Jan 2018 20:50:50 GMT

Try this too

load concat(Field2,',') as A;

LOAD * INLINE [
Field2
1
2
3
];
let x= peek('A');

r:
LOAD * INLINE [
Field1
A
B
C
];

load Field1 as Field4,subfield('$(x)',',') as Field3
resident r ;

Re: How to create row for each record combination in two tables?

rayzac99 — Mon, 29 Jan 2018 17:31:28 GMT

Thanks. This appears to have worked as I need it to.