Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
We value your feedback – take our 5-minute QlikView modernization survey
cancel
Showing results for 
Search instead for 
Did you mean: 
nburton78
Creator
Creator

straight table calculation

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 NumberPart NumberComponent NumberComponent SupplierWOSTAT
19361373SI1.002Part APart A-19999Available to Process
19361373SI1.002Part APart A-28888Supply / Availability
19361373SI1.003Part BPart B-17777Supply / Availability
19361373SI1.003Part BPart B-26666Supply / Availability
19361373SI5.004Part CPart C-15555Supply / Availability
19361373SI5.004Part CPart C-24444Available to Process
19361373SI7.003Part DPart D-13333Available to Process
19361373SI7.003Part DPart D-22222Supply / Availability
19361373SI15Part EPart E-11111Available to Process
19361373SI15Part EPart E-21212Supply / 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 NumberPart NumberComponent NumberComponent SupplierWOSTATSupplier Causer
19361373SI1.002Part APart A-19999Available to Process8888
19361373SI1.002Part APart A-28888Supply / Availability8888
19361373SI1.003Part BPart B-17777Supply / AvailabilityMultiple
19361373SI1.003Part BPart B-26666Supply / AvailabilityMultiple
19361373SI5.004Part CPart C-15555Supply / Availability5555
19361373SI5.004Part CPart C-24444Available to Process5555
19361373SI7.003Part DPart D-13333Available to Process2222
19361373SI7.003Part DPart D-22222Supply / Availability2222
19361373SI15Part EPart E-11111Available to Process1212
19361373SI15Part EPart E-21212Supply / Availability1212

 

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?

Labels (2)
1 Solution

Accepted Solutions
Saravanan_Desingh

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

View solution in original post

9 Replies
jwjackso
Specialist III
Specialist III

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

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.

commQV61.PNG

nburton78
Creator
Creator
Author

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
Creator
Creator
Author

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
Creator
Creator
Author

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
Creator
Creator
Author

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