Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
Kalmer
Partner - Creator
Partner - Creator

Require help with incremental load when dimension changes

Hello!

I'm sturggling to get my code to work. Please help me out guys.
The issue is that i want to increase the value of the dimension "NewField" everytime the LineNr changes also the field "Controll" is there to let me know when i should increase the the value of the dimension.
The red defines how it should be done.
Honestly the Peek function for NewField is not working at all, only the rangesum gives me any result.

concatenate(MasterTable)

Load

    'Journey' as Fact,

    if(Controll=1, RangeSum(1, peek(NewField)+1),

      if(Controll=0, RangeSum(1, peek(NewField)), Peek(NewField))) as NewField,

    *

;

Load

if(Previous(_KEY_ProductID_TS_LineNr) = _KEY_ProductID_TS_LineNr, 0, 1) as Controll

*,

Resident tmp_Jour2

order by Timestamp, ProductID, LineNr. asc;


Untitled.png
Any help is greatfully accepted!

1 Solution

Accepted Solutions
felipedl
Partner - Specialist III
Partner - Specialist III

Hi Kalmer,

I've made a simple example with some dummy data:

x:

load

Fact,

Date,

TimeStamp,

date(Date + TimeStamp,'DD/MM/YYYY hh:mm:ss') as FormattedDate,

LineNr,

ProductID;

load * Inline

[

Fact,Date,TimeStamp,LineNr,ProductID

Journey,01/01/2018,10:05,a,28BE0

Journey,01/01/2018,10:07,a,28BE0

Journey,01/01/2018,10:13,b,28BE0

Journey,01/01/2018,10:25,a,28BE0

Journey,01/01/2018,13:05,c,28BE0

Journey,01/01/2018,16:05,a,28BE0

Journey,01/01/2018,19:00,a,28BE0

Journey,01/01/2018,22:00,d,28BE0

];

Control:

Load

Fact,

Date,

TimeStamp,

date(Date + TimeStamp,'DD/MM/YYYY hh:mm:ss') as FormattedDate,

LineNr,

ProductID,

Control,

if

(

isNull(peek(NewField)),

1,

if

(

Control=1,

peek(NewField)+1,

peek(NewField)

)

) as NewField;

load

*,

if (peek(LineNr)=LineNr,1,0) as Control

Resident x

order by FormattedDate;

drop tables x;

The above script gives me the following:

sample.png

Felipe.

View solution in original post

2 Replies
felipedl
Partner - Specialist III
Partner - Specialist III

Hi Kalmer,

I've made a simple example with some dummy data:

x:

load

Fact,

Date,

TimeStamp,

date(Date + TimeStamp,'DD/MM/YYYY hh:mm:ss') as FormattedDate,

LineNr,

ProductID;

load * Inline

[

Fact,Date,TimeStamp,LineNr,ProductID

Journey,01/01/2018,10:05,a,28BE0

Journey,01/01/2018,10:07,a,28BE0

Journey,01/01/2018,10:13,b,28BE0

Journey,01/01/2018,10:25,a,28BE0

Journey,01/01/2018,13:05,c,28BE0

Journey,01/01/2018,16:05,a,28BE0

Journey,01/01/2018,19:00,a,28BE0

Journey,01/01/2018,22:00,d,28BE0

];

Control:

Load

Fact,

Date,

TimeStamp,

date(Date + TimeStamp,'DD/MM/YYYY hh:mm:ss') as FormattedDate,

LineNr,

ProductID,

Control,

if

(

isNull(peek(NewField)),

1,

if

(

Control=1,

peek(NewField)+1,

peek(NewField)

)

) as NewField;

load

*,

if (peek(LineNr)=LineNr,1,0) as Control

Resident x

order by FormattedDate;

drop tables x;

The above script gives me the following:

sample.png

Felipe.

Kalmer
Partner - Creator
Partner - Creator
Author

Thank you Felip. I managed to solve with a work around.
IsNull() function was used in this case aswell.