Discussion board where members can learn more about Qlik Sense App Development and Usage.
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;
Any help is greatfully accepted!
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:
Felipe.
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:
Felipe.
Thank you Felip. I managed to solve with a work around.
IsNull() function was used in this case aswell.