straight table calculation

[nburton78]

I'm really not even sure what to ask so I apologize in advance.    I have a Qlikview app, it's very big and gets data from multiple sources.  The app takes sales data and feeds into a straight table.  It looks at different criteria and determines a status through a calculation called WOSTAT.    It looks like this so far, each order has multiple components and multiple suppliers:

Order Number	Part Number	Component Number	Component Supplier	WOSTAT
19361373SI1.002	Part A	Part A-1	9999	Available to Process
19361373SI1.002	Part A	Part A-2	8888	Supply / Availability
19361373SI1.003	Part B	Part B-1	7777	Supply / Availability
19361373SI1.003	Part B	Part B-2	6666	Supply / Availability
19361373SI5.004	Part C	Part C-1	5555	Supply / Availability
19361373SI5.004	Part C	Part C-2	4444	Available to Process
19361373SI7.003	Part D	Part D-1	3333	Available to Process
19361373SI7.003	Part D	Part D-2	2222	Supply / Availability
19361373SI15	Part E	Part E-1	1111	Available to Process
19361373SI15	Part E	Part E-2	1212	Supply / Availability

I want to be able to mark each order line with the supplier that is causing the line to be late, like this:

Order Number	Part Number	Component Number	Component Supplier	WOSTAT	Supplier Causer
19361373SI1.002	Part A	Part A-1	9999	Available to Process	8888
19361373SI1.002	Part A	Part A-2	8888	Supply / Availability	8888
19361373SI1.003	Part B	Part B-1	7777	Supply / Availability	Multiple
19361373SI1.003	Part B	Part B-2	6666	Supply / Availability	Multiple
19361373SI5.004	Part C	Part C-1	5555	Supply / Availability	5555
19361373SI5.004	Part C	Part C-2	4444	Available to Process	5555
19361373SI7.003	Part D	Part D-1	3333	Available to Process	2222
19361373SI7.003	Part D	Part D-2	2222	Supply / Availability	2222
19361373SI15	Part E	Part E-1	1111	Available to Process	1212
19361373SI15	Part E	Part E-2	1212	Supply / Availability	1212

is this even possible in Qlikview?  I've tried doing an if statement like "if WOSTAT = 'Supply / Availability', Component Supplier" but when there's more than 1 supplier it returns (-).  Any idea?

[jwjackso]

It displays the '-' because there are multiple Component Suppliers and only 1 line displayed.

Try something like:

=If(WOSTAT = 'Supply/Availability',If(Count([Component Supplier]) > 1,'Multiple',[Component Supplier]),'SOMETHING HERE WHEN WOSTAT <> SUPPLY/AVAILABILTY')

[Kushal_Chawda]

What is the calculation for WOSTAT?

[Saravanan_Desingh · Accepted solution]

One solution is.

tab1:
LOAD * INLINE [
    Order Number, Part Number, Component Number, Component Supplier, WOSTAT
    19361373SI1.002, Part A, Part A-1, 9999, Available to Process
    19361373SI1.002, Part A, Part A-2, 8888, Supply / Availability
    19361373SI1.003, Part B, Part B-1, 7777, Supply / Availability
    19361373SI1.003, Part B, Part B-2, 6666, Supply / Availability
    19361373SI5.004, Part C, Part C-1, 5555, Supply / Availability
    19361373SI5.004, Part C, Part C-2, 4444, Available to Process
    19361373SI7.003, Part D, Part D-1, 3333, Available to Process
    19361373SI7.003, Part D, Part D-2, 2222, Supply / Availability
    19361373SI15, Part E, Part E-1, 1111, Available to Process
    19361373SI15, Part E, Part E-2, 1212, Supply / Availability
];

Left Join(tab1)
LOAD [Order Number], [Part Number], 
		If(Count(If([WOSTAT]='Supply / Availability',[Component Supplier]))>1, 
			'Multiple', 
			Concat(If([WOSTAT]='Supply / Availability',[Component Supplier]))
		) As [Supplier Causer]
Resident tab1
Group By [Order Number], [Part Number];

[Saravanan_Desingh]

Output.

[nburton78]

Here's a sample of what I'm doing, maybe this will make more sense.  

In the All data table:

WOSTAT: determines the category (Available to Process, Supply / Available, In Process, Order not Acknowledged) based on criteria 

rank: This provides a rank to each WOSTAT.  Since there are 2 or more components to each order I need to know which category has the most priority.  If one component is Supply / Available, then all Components are moved to that category.  

Category:  assigns the text name to the max rank

The overall goal is to remove the components and have one line for each ordernumber as shown in the Discrete Data table( at the bottom) and so far everything works, except the supplier causer.  

This is a simplified version, my real version has 8 tables joined in various ways and other calculations in the table

[nburton78]

The one idea I had was what if I can move the calculations from the chart to the script?  Is that possible?  not really sure how that would work.  especially with multiple tables.  

[Kushal_Chawda]

What could be supplier causer output for each order in Discrete all table?

[nburton78]

supplier causer would be the supplier where Supply / Availability is the category.  so If  there's 2 component lines one is Supply / Availability and the other is anything else, I only want to see the supplier that is causing the late.  If there is more than one supplier causing a late I want it to say "Multiple".   

[nburton78]

I moved the calculation to the load script and then created the field "supplier causer" off that calculation.