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]

Can you divide the AMOUNT between the two countries like this may be:

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

LEFT JOIN (TABLE1)

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

Join(TABLE1)

LOAD Key,

          Avg(AMOUNT) as NewAMOUNT

Resident TABLE1

Group By Key;

TABLE1:

LOAD * INLINE [

Key, AMOUNT

1111, 300

];

LEFT JOIN (TABLE1)

LOAD * INLINE [

Key, Country

1111, USA

1111, INDIA

];

Join(TABLE1)

LOAD Key,

     Avg(AMOUNT)/Count(AMOUNT) as NewAMOUNT

Resident TABLE1

Group By Key;

Where NewAmount will be 150 and 150 and when you sum it (Sum(NewAmount)) you will get 300

[jonathandienst]

Ok, but you did not have field that in your original question. I suspect that there is no way to get this data into a single table without a better understanding of the business rules, as a I said in my earlier post.

[Anonymous]

hi

Can we use Distinct sum(Amount)  ?

Sum(Distinct Amount)

Please Excuse me if it is wrong

[sasiparupudi1]

Table1:

LOAD * INLINE [

Key, AMOUNT

1111, 300

];

NoConcatenate

Table2:

LOAD

  *INLINE [

Key, Country, Currency

1111, USA, Dollar

1111, USA, Rupee

];

Country:

Mapping LOAD

Key, Country Resident Table2;

Currency:

Mapping LOAD

Key, Currency Resident Table2;

NoConcatenate

Final:

LOAD Key, AMOUNT,ApplyMap('Country',Key) as Country,ApplyMap('Currency',Key) as Currency  Resident Table1;

drop Table Table1,Table2;

[maxgro]

TABLE1:

LOAD * INLINE [

Key, AMOUNT

1111, 300

];

TABLE2:

LOAD * INLINE [

Key, Country

1111, USA

1111, INDIA

];

QvdFile:

load 'TABLE1' as Table, * Resident TABLE1;

Concatenate (QvdFile) load 'TABLE2' as Table, * Resident TABLE2;

STORE QvdFile into QvdFile.qvd (qvd);

[jagan]

Hi,

With this type of data you should not join both the tables, just keep both the tables separately and if you want to store in QVDs store it in two QVDs.  You should not always join the tables.

Hope this helps you.

Regards,

jagan.

[qlikviewwizard]

Hi Eric,

Did you able to resolve your issue? Thank You.

[engishfaque]

Dear Eric,

=Sum(Distinct Amount)

Kind regards,

Ishfaque Ahmed