Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hi!
Why would you like to have 'aaa' in your output table as a name if it is actually a code?
Jaime
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
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.
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.
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