Showing a value in a Table box based on several assumptions

Wed, 14 Jan 2015 08:34:12 GMT

I´m trying to make a report of how much a subcontractor should invoice me for some tasks made. The price is based on what type of task made, where it have been made and if it is done.

I loaded 4 excel list into Qlikview:

1) Case number and Task Type (Note that case on this list is the case that is done and should be on the invoice list)

Case Number	Task type
10001	Task A
10003	Task C
10004	Task C

2) Case number, task type, Subcontractor & Area where the case was done (Note that this contains all case numbers but not the task type)

Case Number	Subcontractor	Area
10001	Subco X	Area A
10002	Subco Y	Area D
10003	Subco X	Area D
10004	Subco X	Area F

3) Area & Price level

Area	Price Level
Area A	Price Level 1
Area B	Price Level 1
Area C	Price Level 4
Area D	Price Level 2
Area F	Price Level 3

4) Price list:

Task type	Price level 1	Price level 2	Price level 3	Price level 4
Task A	100	200	300	400
Task B	200	250	300	350
Task C	300	350	400	450

And I would like to get a list like the below when I select Subco X in a Listbox, but I can not get the relation right (Note that case 10002 is missing as that is not included in the list with case done):

Case Number	Task Type	Area	Price from Price list
10001	Task A	Area A	100
10004	Task A	Area F	400

topic Showing a value in a Table box based on several assumptions in QlikView

Showing a value in a Table box based on several assumptions