topic Re: Issue in Join two tables in QlikView

Issue in Join two tables

poooja — Tue, 14 May 2019 02:45:31 GMT

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

Re: Issue in Join two tables

PrashantSangle — Tue, 14 May 2019 03:47:48 GMT

try below

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

Regards,
Prashant Sangle

Re: Issue in Join two tables

jaibau1993 — Tue, 14 May 2019 06:24:27 GMT

Hi!

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

Jaime

Re: Issue in Join two tables

poooja — Tue, 14 May 2019 06:43:07 GMT

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

Re: Issue in Join two tables

jonathandienst — Tue, 14 May 2019 06:56:57 GMT

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.

Re: Issue in Join two tables

Vegar — Tue, 14 May 2019 06:56:41 GMT

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.

Re: Issue in Join two tables

sultanam — Tue, 14 May 2019 07:09:22 GMT

Hi Pooja,

try below one.
A:

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

Left Join (A)

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