Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Read about the latest Qlik Community enhancements on the Community News blog!
cancel
Showing results for 
Search instead for 
Did you mean: 
poooja
Creator
Creator

Issue in Join two tables

Hi Qlikis,

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

Question:
Table 1 has total amount spend by shopper.

Tablea513.JPG
Table 2 has details for those expenses like categories.

tableb513.JPG

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

answer.JPG

 

Find attached Qlik file.. 

Thanks!

Pooja

6 Replies
PrashantSangle

try below

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

Regards,
Prashant Sangle
Great dreamer's dreams never fulfilled, they are always transcended.
jaibau1993
Partner
Partner

Hi!

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

Jaime

poooja
Creator
Creator
Author

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

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.

 

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Vegar
MVP
MVP

image.png

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. 

Plees ekskuse my Swenglish and or Norweglish spelling misstakes
sultanam
Contributor III
Contributor III

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.

NameCategoryPrice
  140
GeorgePen20
GeorgePencil10
Georgeink20
Peterfood45
PeterTravel45

 


Regards,
Sultan