JOIN, KEEP OR CONCATENATE?

[psk180590]

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!!

[Clever_Anjos · Accepted solution]

Basically

T:

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;

[Anil_Babu_Samineni]

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;

[effinty2112]

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