Unable to script tables as expected

[laureadiaz]

Hello,

I have been using a manual table for price break quantity and pricing, but now it is in our ERP system and I would like to make it match my manual table for all my equations to keep working.

Ex of manual table:

Short_ID  Mastered_Item    Q1    P1      Q2    P2       Q3     P3        Q4      P4      Q5       P5
1346     ABS0114T3-7            100   1.33  250  1.188  500  1.128   1000  1.04   2500   0.74
1347     ABS0114T4-13         100   3.5     250  2.5       500  2.15      0         0          0          0

Closest I can get:

Short_ID Mastered_Item           Q1      P1       Q2      P2      Q3      P3      Q4      P4      Q5      P5
1347          1347ABS0114T4-13   100    0.35     0         0        0          0       0            0        0          0
1347          1347ABS0114T4-13    0           0      250    2.5       0          0       0           0        0          0
1347          1347ABS0114T4-13    0           0        0          0     500    2.15     0           0        0          0

I tried summing the Q and Ps but got an "Unknown load error"

Please see my attached mini version for what I have tried.

Thank you,

[rubenmarin]

Hi, yu can create a similar table using sums...P1 is 0.35 instead of 3.5 and P2 is 0.25 intead of 2.5 , isn't?

To create the table from script you'll need a group by to join data, ie:

Price_Breaks:
Load 
Short_ID&Mastered_Item as PBreak_Key,
Short_ID,
Mastered_Item,
FirstValue(Qty1) as Q1,
FirstValue(Pr1) as P1,
...
Resident 
Price_Breaks_Temp
Group by Short_ID, Mastered_Item
;

[laureadiaz]

Thanks,

I just tried using First Value for all Qs and Ps.

but my table had Q1 and P1 but no other price breaks just blanks.

But this does give me an idea to play with thanks!

[laureadiaz · Accepted solution]

I got this to work:

change the first table to get rid of 0 if null.

then

IF (IsNull(FirstSortedValue(Qty1,Pr1,1)),0,FirstSortedValue(Qty1,Pr1,1)) as Q1,
IF (IsNull(FirstSortedValue(Pr1,Qty1,1)),0,FirstSortedValue(Pr1,Qty1,1)) as P1,
IF (IsNull(FirstSortedValue(Qty2,Pr2,1)),0,FirstSortedValue(Qty2,Pr2,1)) as Q2,

.....

Now I get the number I need or a 0 if no number for my equations to work.

Now to just clean up the data as they do not all match between my manual table and what is in the ERP (ha, which one is right?)