I need to read a column of data.. first figure out if it is one of my "master parts", then label it, but the master parts are a set of 2-30 so its a lot of "or" as I can not in Qlik sense referance a master colum sheet.
Part Num
Ship Quantity
Assembled Units Ship Qty
Sales Order
INV USD Ext Cost
4722-WE2-5912
1
0
51282
234.42
4722-WE2-0000
1
12567821
51282
1323.04
4722-WE2-2924
1
0
51282
12.4
4722-WE2-6201
1
0
51282
24.67
2733-ITL-4005
10
0
51282
52.07
2733-ITL-4012
15
0
51282
62.78
2733-ITL-4013
4
0
51282
50.80
2733-ITL-4014
14
0
51282
234.84
5733-OMF-0000
1
678824
34282
5610.54
5733-OMF-2001
1
0
34282
1.12
5733-OMF-2935
1
0
34282
43.65
5733-OMF-5839
1
0
34282
13.07
6733-XT2-0000
1
46781
63411
1385.00
6733-XT2-2001
1
0
63411
145.05
6733-XT2-5819
1
0
63411
75.03
So I did some RTVM on dimentions and some some of the puzzle done but am running out of time. If you post, and know.. please post to where you found / know where the M exists.
Goal 1: Sort by "Assembled Units Ship Qty" to show how many of each unit was shipped. But the only way I know if the unit was "the assembled product (which had sub components) is the "-0000" at the end. Then I have to catagorize them based on model. So I read in the first 8 characters and do a comparison with lots of "or" to make a label. I can then use this master dimention throughtout the app.
This works (the 2+2=4) showing I can add an "or" ... but ...
=if(right([First Eight of Product No],5 = '-0000' and left([First Eight of Product No],8 = '4722-WE2')) or 2+2=4,'LargeSystem')
This does not (Error in expression)
=if(right([First Eight of Product No],5 = '-0000' and left([First Eight of Product No],8 = '4722-WE2')) or (right([First Eight of Product No],5 = '-0000' and left([First Eight of Product No],8 = '6733-XT2')),'LargeSystem')
The next.. and I have no idea now to do this is to calculate the value of the sale for each unit. But the "INV USD Ext Cost" for the base product (line that ends in '-0000') is garbage. Data guys not sure where it is getting that value from but it is not relavant.
So I have to add up "all units in a sales order" and tell what it was sold for broken down by unit. Ya.. and sometimes the master product (ending in '-0000') is not the first line of the list.
Logic to me is something like: For each sales order.. sum up [INV USD Ext Cost] but then subtract [INV USD Ext Cost] for when Product ends in -0000...
Swag at code:
For [Sales Order] ((Sum [INV USD Ext Cost] by Part Number ) - (if(right([First Eight of Product No],5 = '-0000' and left([First Eight of Product No],8), [INV USD Ext Cost] )
Ok.. thanks for correction on brackets.. but I still am not able to add an "or" to then create my master list of models.
=if(right([First Eight of Product No],5) = '-0000' and (left([First Eight of Product No],8) = '8005-12N') or 2+2=4,'LargeSystem')
That Expression works.. I am using "or 2+2=4," as a place holder of simple formula to that I then insert the "correct one" ... but when I do..
=if(right([First Eight of Product No],5) = '-0000' and (left([First Eight of Product No],8) = '1205-12N') or (right([First Eight of Product No],5) = '-0000' and (left([First Eight of Product No],8) = '8235-22N'),'LargeSystem')
=if(right([First Eight of Product No],5) = '-0000' and (left([First Eight of Product No],8) = '1205-12N') or (right([First Eight of Product No],5) = '-0000' and (left([First Eight of Product No],8) = '8235-22N')),'LargeSystem')
As such my last piece is the calculation of the how much a selected model or group sold is going to be very large in code calculation then... but maybe (hopefully) I am making this too hard..
Goal: If someone says to me: How many of those "Assembled Units" shipped.. I can tell them. But if they say how much did we by the parts at, and sell them at..(aka then profit) I am now three layers deep
Step 1: they select the dimension "LargeSystem" (or one of the others I now have built) but then have to sum up sales but then subtract the values when the line has (right([Part Num],5) = '-0000'
