Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi everyone,
I am facing a problem with a table I am reading. Table has some average weight ranges, but has only "From" values as below. I would like to calculate "To" column inside the script. Columns "TableID" and SizeID are also coming from the table and I need them.
TableID | From | To | SizeID | Calcuation Notes |
{3D643767} | 900 | 99999 | 4 | TableID Start -> static value 99999 |
{3D643767} | 900 | 99999 | 5 | TableID Start -> static value 99999 |
{3D643767} | 900 | 99999 | 6 | TableID Start -> static value 99999 |
{3D643767} | 800 | 899,999 | 4 | From changes, has to be (previous(From)-0.001) |
{3D643767} | 800 | 899,999 | 5 | From remains, Previous(To) |
{3D643767} | 800 | 899,999 | 6 | From remains, Previous(To) |
{3D643767} | 700 | 799,999 | 3 | From changes, has to be (previous(From)-0.001) |
{3D643767} | 700 | 799,999 | 4 | From remains, Previous(To) |
{3D643767} | 700 | 799,999 | 5 | From remains, Previous(To) |
{3D643767} | 700 | 799,999 | 6 | From remains, Previous(To) |
{3D643767} | 600 | 699,999 | 2 | From changes, has to be (previous(From)-0.001) |
{3D643767} | 600 | 699,999 | 3 | From remains, Previous(To) |
{3D643767} | 600 | 699,999 | 4 | From remains, Previous(To) |
{3D643767} | 600 | 699,999 | 5 | From remains, Previous(To) |
{89454FAA} | 1200 | 99999 | 4 | TableID Start -> static value 99999 |
{89454FAA} | 1200 | 99999 | 5 | TableID Start -> static value 99999 |
{89454FAA} | 1200 | 99999 | 6 | TableID Start -> static value 99999 |
{89454FAA} | 800 | 1199,999 | 4 | From changes, has to be (previous(From)-0.001) |
{89454FAA} | 800 | 1199,999 | 5 | From remains, Previous(To) |
{89454FAA} | 800 | 1199,999 | 6 | From remains, Previous(To) |
{89454FAA} | 700 | 799,999 | 3 | From changes, has to be (previous(From)-0.001) |
{89454FAA} | 700 | 799,999 | 4 | From remains, Previous(To) |
{89454FAA} | 700 | 799,999 | 5 | From remains, Previous(To) |
{89454FAA} | 700 | 799,999 | 6 | From remains, Previous(To) |
I tried some combinations of "if" with "previous" but did not work. Could someone please help?
Thank you in advance for devoting your time
maybe like this?
table1:
LOAD TableID,
From,
If(TableID=Previous(TableID),If(From=Previous(From),Peek(To),Previous(From)-0.001),99999) as To,
SizeID
FROM [https://community.qlik.com/t5/QlikView-App-Dev/Script-calculation-based-on-previous-values/td-p/1901845] (html, codepage is 1252, embedded labels, table is @1);
maybe like this?
table1:
LOAD TableID,
From,
If(TableID=Previous(TableID),If(From=Previous(From),Peek(To),Previous(From)-0.001),99999) as To,
SizeID
FROM [https://community.qlik.com/t5/QlikView-App-Dev/Script-calculation-based-on-previous-values/td-p/1901845] (html, codepage is 1252, embedded labels, table is @1);
@MarcoWedel you are great. I learned the function 'Peek'. Many thanks!