Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
New Contributor

Lookup and sum values to create a table in load script

Hi all

Just looking for a quick steer on how to do something. I want to create a table (C) in the load script of summed values from one table (A) based on another table (B).

So for example say Table A is all fruit sales for multiple products, suppliers and months, like this:

Order

Product

Supplier

Month

Sales

1

Apples

Supplier1

Jan-18

120

2

Apples

Supplier1

Jan-18

65


Table B is effectively a subset of products, suppliers and months (without the sales volume) but distinct rows 

Product

Supplier

Month

Apples

Supplier1

Jan-18

 

So how can I look up multiple fields from B (Apple, Supplier1,Jan 2018) to find a matching rows in A and sum the relevant Sales Volumes to create a new table C. Repeating for other product supplier month combinations (all rows in table B basically). 

Giving me Table C:

Product

Supplier

Month

SalesTotal

Apples

Supplier1

Jan-18

185

 

What method/functions should I use? Are MappingLoad/ApplyMap are useful here or is it a Sum If type expression (as it would be in Excel)? Any help appreciated!

Labels (2)
4 Replies
Highlighted
Partner
Partner

Re: Lookup and sum values to create a table in load script

There you go:

clipboard_image_0.png

  1. Load the supplier table.
  2. Left keep Supplier table with the Order table.
  3. In this example, I have added a new row(Oranges) in Order table so that the result after left keeping with Supplier table can be shown.
  4. At front end, create a straight table and create sum(Sales) as measures.

Refer qvw attached as reference.

Thanks and regards,

Arthur Fong

Highlighted
Employee
Employee

Re: Lookup and sum values to create a table in load script

TableC:
Load Product,
Supplier,
Month,
Sum(Sales) as TotalSales
Resident TableA
Group by Product, Supplier, Month;

This will give you a synthetic key, but will be correct. I do not understand the purpose of Table B
Highlighted
Digital Support
Digital Support

Re: Lookup and sum values to create a table in load script

Daryl, did Arthur or Lisa's post help you get things working?  If so, be sure to circle back and use the Accept as Solution button on the post(s) that helped you.  If you did something different, consider posting that for others, so they will know what worked, and you can mark that post using the button after you save it.  If you are still working upon things, I have a Design Blog post that might be another way to go at things, I think it may fit your scenario, but I am not sure.  I will post the main blog link as well in case you want to search that area on your own as well...

https://community.qlik.com/t5/Qlik-Design-Blog/Unbalanced-n-level-hierarchies/ba-p/1474325

https://community.qlik.com/t5/Qlik-Design-Blog/Bill-of-Materials/ba-p/1462792

https://community.qlik.com/t5/Qlik-Design-Blog/bg-p/qlik-design-blog

Added in three total, sorry, found another one that might fit, hopefully this helps you move things forward if you are still working on them.

Regards,
Brett

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.
Highlighted
New Contributor

Re: Lookup and sum values to create a table in load script

Sorry I haven't had chance to respond as have been asked to work on something else. Thank you for the replies. I will respond as soon as I get chance to look at them properly.