OFFSET in Knime

KNIME Analytics Platform
1

Hi all,

I’m new in Knime, i was using SPSS Modeler but now i need to transform my flow to Knime. In SPSS Modeler, there is a useful function(@OFFSET) to take lag and update old value and calculate again. For example;

there is a column named x(volume) and its values are 1, 0, 3, 0, 0, 0 and i want to create y(how many days have no sales) column as; 0,1,0,1,2,3. It means if x>0 y=0; else y starts with 1 and increases +1 while x=0

Notes: I have millions of data and don’t want to use python because it is very slow owing to taking data on its platform. Also, i don’t want to use loop in Knime because i have 6000 partition(like stores) and lots of date.

Please help urgently

2

KNIME may not be very fast for you. You can try Java Snippet for speed. Anyway. PSPP free SPSS clone may work better for you.

3

Well I would have used a database from Knime for that. But If you have a good machine you can try something like that :

example2.knwf (66.7 KB)

And yes I know what you all think but there is no Python and no loop ! :o)

2 Likes
4

I know it is too much Fabien, but the project must be work daily and there are lots of stores, other alternatives take long time. i havent tried only java snipped and i am going to look at your flow. I found a presentation below which includes these function in knime whislist. I have thought maybe it could has been developed.

page:68

5

Where are your datas at the begining ? Is there a possibility to use a database ? The java way will be in that case the best way.

6

Yes, my datas at database but i have never used java before …thinking

7

Then if you can connect via the database via knime using the db nodes, you could do it as a query if your database allow the windowing functions. You will avoid the time to transport your datas from the database to Knime in that case.

1 Like
8

Hi @yunusEG and welcome to the KNIME community forum,

I have built a workflow which is almost similar to the solution by @Fabien_Couprie but a bit shorter.

offset.knwf (53.9 KB)

No loops and no code.

:blush:

4 Likes
9

Thanks both of you. It will be useable for my case. I have one more problem and maybe you can help about offset logic in Knime like that a data.

store product date stock stock_1
1 p1 20190101 phantom phantom
1 p1 20190102 phantom phantom
1 p1 20190103 phantom soos

1 p2 20190101 phantom normal
1 p2 20190102 phantom normal
1 p2 20190103 phantom normal
1 p2 20190104 phantom normal
1 p2 20190105 normal normal
1 p2 20190106 soos soos

First 4 columns are input columns and i am trying to create last column. Rule is;

if stock is normal and lag of its all phantom stock rows must be replaced with normal until i.th lag of stock not equal to phantom partition by store and product else stock=stock_1.

10

Sorry for bad typing, i am going to fix it and share again

11

updated topic is below;

Thanks everybody

1 Like
12

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.