Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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];
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')
What is the calculation for WOSTAT?
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];
Output.
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
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.
What could be supplier causer output for each order in Discrete all table?
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".
I moved the calculation to the load script and then created the field "supplier causer" off that calculation.