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

Lookup and sum values to create a table in load script

Dazzler — Sat, 16 Nov 2024 20:00:09 GMT

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!

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

Arthur_Fong — Fri, 27 Sep 2019 00:23:39 GMT

There you go:

Load the supplier table.
Left keep Supplier table with the Order table.
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.
At front end, create a straight table and create sum(Sales) as measures.

Refer qvw attached as reference.

Thanks and regards,

Arthur Fong

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

Lisa_P — Fri, 27 Sep 2019 00:56:29 GMT

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

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

Brett_Bleess — Tue, 08 Oct 2019 13:08:40 GMT

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

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

Dazzler — Tue, 08 Oct 2019 13:29:48 GMT

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.