QVD - USING LEFT JOIN BUT NOT LINK TABLE

[#1]

Dear All

I have one question?

My Tables are Like this:

TABLE1:
LOAD * INLINE [
Key, AMOUNT
1111, 300
];

LEFT JOIN

TABLE2:
LOAD * INLINE [
Key, Country
1111, USA
1111, INDIA
];

I want to create a QVD using 2 Tables.

When I am doing Left Join, it creates one Table and the Frequency of Key 1111 becomes 2.

So, if I do Sum(AMOUNT) than it will show Sum 600 instead of 300 which is wrong.

But, if I just Link these 2 Tables instead of doing Join than it will show SUM(AMOUNT) 300 which is Right.

But, I have to create a QVD by using these 2 tables.

Is there any Alternative.

Regards

Eric

[sunny_talwar]

Use this expression if you have to join them to form a qvd:

=Sum(Aggr(Avg(AMOUNT), Key))

[alexandros17]

You can create 2 QVD or when you show sum(Amount) you can aggregate by key

[jonathandienst]

It depends on what the duplicate values of the Key in Table2 mean. If it is a dimension value change (such as a slowly changing dimension), then you would need some way of selecting a value, such as effective date ranges or status flag of some sort.

Or if they are effectively a list, concatenate the values like this:

LEFT JOIN

LOAD Key,

  Concat(Country, ', ') As Countries

INLINE [

Key, Country

1111, USA

1111, INDIA

];

Or perhaps its a data quality issue.

You need to understand the business meaning of the duplicate values to determine the 'correct' way to handle them.

[#5]

I have to achieve this is Back end Script.

I have to create single QVD. Is that possible.

[#6]

I have to achieve this is Back end Script.

I have to create single QVD. Is that possible.

[PrashantSangle]

Hi,

Try this,

TABLE1:
LOAD * INLINE [
Key, AMOUNT
1111, 300
];

LEFT JOIN

LOAD * INLINE [
Key, Country
1111, USA
1111, INDIA
];

FINAL:
LOAD Key,
Country,
SUM(AMOUNT) AS SUM_AMOUNT
RESIDENT TABLE1
GROUP BY Key,Country;

DROP TABLE TABLE1;

Which will give you one table at the end.

Regards

[#8]

Dear Max

Wrong Sum is coming as per your Code.

I need the Amount to be display as 300

[alexandros17]

Unfortunately you cannot achieve that result because left join Always duplicates rows.

[#10]

Dear Jonathan

Your Code will cause problem in this case:

TABLE1:
LOAD * INLINE [
Key, AMOUNT
1111, 300
];

LEFT JOIN

LOAD
  Concat(Country, ', ') As Countries INLINE [
Key, Country, Currency
1111, USA, Dollar
1111, USA, Rupee
];

How to avoid this Concat() Result.

Regards

Eric