Issue in Join two tables

[poooja]

Hi Qlikis,

Need a help on this. This is a sample based on my requirement.

Question:
Table 1 has total amount spend by shopper.

Table 2 has details for those expenses like categories.

As per table 1, Total amount spent by Peter is 90 for different category. By doing left join i am not able to get the right amount for category wise. Suggest me how to join the table to get the below output.

Output with Right Answer

Find attached Qlik file.. 

Thanks!

Pooja

[PrashantSangle]

try below

Load distinct code, shopper from table1;
Right Join
Load code,category,price from table2;

Regards,
Prashant Sangle

[jaibau1993]

Hi!

Why would you like to have 'aaa' in your output table as a name if it is actually a code?

Jaime

[poooja]

This is not working.  I am not able to get expected output.

Also i cannot do distinct code here as my corporate account keep on adding some lines in the table1 with some new amounts.

Please any other idea! 

Thanks, 

Pooja

[jonathandienst]

The solution looks correct, except perhaps you need a left join or an outer join and you may need to align the field names with your data.

>>This is not working.  I am not able to get expected output.

Its not possible to solve a problem with this amount of information. What did you try? What did you get? What is wrong with the output you got.....

>>Also i cannot do distinct code here as my corporate account keep on adding some lines in the table1 with some new amounts.

This does not make sense to me. Adding new lines to table 1 does not conflict with using a distinct. The distinct prevents doubling of the data if a name or code is duplicated. If you don't like this you could use an ApplyMap to add the names to table2.

[Vegar]

Your output expecting  [aaa, inc, 20] seems odd to me. The code aaa will join with George. 

Please note that when joining table2 to the final amount table you will not be able to sum final_amt no more as you will duplicate the final_amount to every code matching row.

Please find attached qvw. 

[sultanam]

Hi Pooja,

try below one.
A:

LOAD Code,
Shpper as Name,
Final_amt
FROM
Source A;

Left Join (A)

B:

LOAD Code,
Price,
Category
FROM
Source B;

Do the calculation in straight table

Take dimensions as Name and Category
Measure as Sum(Price)

You will have the below output.

Name	Category	Price
		140
George	Pen	20
George	Pencil	10
George	ink	20
Peter	food	45
Peter	Travel	45

Regards,
Sultan