topic Re: JOIN, KEEP OR CONCATENATE? in QlikView

JOIN, KEEP OR CONCATENATE?

psk180590 — Wed, 25 Nov 2020 16:16:04 GMT

Hello All,

I have data from two tables.

Table1:

Product	Sales
A	10
B	20
C	30
D	40

Table 2:

Product	Sales
A	50
B	30
C	45
T	23
Z	60

Expected Result:

Product	Sales
A	60
B	50
C	75
D	40

Basically, i need to show only the Products that are only in Table 1 along with the sum of sales from Table 2.

Also, these are around 6 M records so would also be good to have performance in mind.

Thanks!!

Re: JOIN, KEEP OR CONCATENATE?

Clever_Anjos — Wed, 11 Oct 2017 12:02:19 GMT

Basically

Load Product, Sales

From yourtable1qvd(qvd);

concatenate(T)

Load Product, Sales

From yourtable2qvd(qvd)

Where exists(Product);

Table:

noconcatenate Load // maybe can take long time (group by operations are very expensive)

Product,

Sum(Sales) as Sales

Resident T

Group by Product;

Drop table T;

Re: JOIN, KEEP OR CONCATENATE?

Anil_Babu_Samineni — Wed, 11 Oct 2017 12:05:17 GMT

Here you go

Table1:

LOAD * Inline [

Product , Sales

A, 10

B ,20

C, 30

D, 40

];

Concatenate(Table1)

Table2:

LOAD * Inline [

Product , Sales

A, 50

B, 30

C, 45

T, 23

Z, 60

] Where Exists(Product);

Final:

NoConcatenate

LOAD Product,

Sum(Sales) as Sales

Resident Table1

Group by Product;

Drop table Table1;

Re: JOIN, KEEP OR CONCATENATE?

effinty2112 — Wed, 11 Oct 2017 12:05:40 GMT

Hi,

Try

Data:

Load Product, Sales From Table1;

Concatenate(Data)

Load Product, Sales From Table1

Where Exists(Product);

Then in the UI a straight table with dimension Product and expression Sum(Sales) or instead in script:

Noconcatenate

[Aggregated Data]:

Load

Product,

sum(Sales) as Sales

Resident Data Group by Product;

Drop table Data;

Regards

Andrew