topic Re: straight table calculation in QlikView

straight table calculation

nburton78 — Sat, 16 Nov 2024 00:09:45 GMT

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?

Re: straight table calculation

jwjackso — Tue, 11 Aug 2020 21:12:37 GMT

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')

Re: straight table calculation

Kushal_Chawda — Tue, 11 Aug 2020 21:54:41 GMT

What is the calculation for WOSTAT?

Re: straight table calculation

Saravanan_Desingh — Tue, 11 Aug 2020 23:21:09 GMT

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];

Re: straight table calculation

Saravanan_Desingh — Tue, 11 Aug 2020 23:22:07 GMT

Output.

Re: straight table calculation

nburton78 — Wed, 12 Aug 2020 12:49:05 GMT

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

Re: straight table calculation

nburton78 — Wed, 12 Aug 2020 14:27:41 GMT

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.

Re: straight table calculation

Kushal_Chawda — Wed, 12 Aug 2020 14:36:27 GMT

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

Re: straight table calculation

nburton78 — Wed, 12 Aug 2020 14:44:19 GMT

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".

Re: straight table calculation

nburton78 — Thu, 13 Aug 2020 16:13:34 GMT

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