topic Re: Unable to script tables as expected in QlikView

Unable to script tables as expected

laureadiaz — Sat, 16 Nov 2024 02:53:00 GMT

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,

Re: Unable to script tables as expected

rubenmarin — Mon, 29 Jul 2019 21:05:28 GMT

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 ;

Re: Unable to script tables as expected

laureadiaz — Tue, 30 Jul 2019 13:43:03 GMT

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!

Re: Unable to script tables as expected

laureadiaz — Tue, 30 Jul 2019 14:05:36 GMT

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