Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I would like to know how to refer to a variable from the previous perido. The variable period is defined as a number: 1..36 (like months).
The table is like the following:
customerId | period | alarm |
---|---|---|
4 | 26 | 0 |
4 | 27 | 2 |
4 | 28 | 0 |
4 | 29 | 3 |
4 | 30 | 0 |
4 | 31 | 1 |
4 | 32 | 1 |
4 | 33 | 0 |
4 | 34 | 0 |
4 | 35 | 0 |
4 | 36 | 0 |
I want to show in Qlik the following table
customerId | period | alarm | prevAlarm | Difference |
---|---|---|---|---|
4 | 27 | 2 | 0 | 1 |
4 | 28 | 0 | 2 | 0 |
4 | 29 | 3 | 0 | 1 |
4 | 30 | 0 | 3 | 0 |
How could I calculate prevAlarm? Difference is 1 if the current alarm >0 and the previous was <=0. I have the insight of how to do this, but I need to refer to prevAlarm.
Thank you,
Try this.
Load *, if(alarm >0 and PrevAlarm <=0, 1,0) as Difference;
Load CustomerID, Period, alarm, previous(alarm) as PrevAlarm
From xyz;
Regards,
Kaushik Solanki
Try this.
Load *, if(alarm >0 and PrevAlarm <=0, 1,0) as Difference;
Load CustomerID, Period, alarm, previous(alarm) as PrevAlarm
From xyz;
Regards,
Kaushik Solanki
Hi,
You will have to change logic for more than one customerId in your data
NoConcatenate
AlarmTmp:
LOAD customerId
,period
,alarm
FROM
[..\Data\Test12.xlsx]
(ooxml, embedded labels, table is Sheet1);
//order by customerId desc, period desc;
NoConcatenate
AlarmData:
LOAD customerId
,alarm as prevperiodalarm
,period as prevperiod
,Peek(alarm, RowNo(), 'AlarmTmp') as periodalarm
,Peek(period, RowNo(), 'AlarmTmp') as period
// ,If(RowNo()=1, Null(), Peek(alarm, RowNo(), 'AlarmTmp')) as periodalarm
// ,If(RowNo()=1, Null(), Peek(period, RowNo(), 'AlarmTmp')) as period
Resident AlarmTmp;
//order by customerId desc, period desc;
DROP Table AlarmTmp;
expression
=If(periodalarm > 0 and prevperiodalarm <= 0, 1, 0)