topic Re: increment value after any change in QlikView

increment value after any change

JMAROUF — Sat, 16 Nov 2024 18:10:27 GMT

Hi everyone,

i want to increment a value after any variable's change, i've used autonumber(FIELD1,FIELD2) it works but when FIELD1 gets the first value autonumber gives the old value example:

field1 | field1 | number

xx val 1

yy val 2

xx val 1

and this is what i want to do:

xx val 1

yy val 2

xx val 3

Thank you for advance;

Re: increment value after any change

Taoufiq_Zarra — Sun, 30 Aug 2020 19:34:07 GMT

One solution for example :

Table1: load *,if(rowno()=1,1,peek(number)+1) as number inline [ field1,field2 xx,val yy,val xx,val ]

output:

Re: increment value after any change

JMAROUF — Sun, 30 Aug 2020 22:25:40 GMT

Hi Taoufiq,

Thank you so much for your answer, i've used autonumber() because i have some polices which change intermediate (SLOWLY CHANGING DIMENSION):

field1 | field1 | date

xx val 01/01/2015

xx val 01/01/2016

yy val 01/01/2017

yy val 01/01/2018

yy val 01/01/2019

xx val 01/01/2020

AND WANT THIS:

field1 | field1 | number | date

xx val 1 01/01/2015

xx val 1 01/01/2016

yy val 2 01/01/2017

yy val 2 01/01/2018

yy val 2 01/01/2019

xx val 3 01/01/2020

Re: increment value after any change

JMAROUF — Mon, 31 Aug 2020 09:12:19 GMT

Hello @Taoufiq_Zarra ,

Thank you for your answer, i've used autonumber for the ( SLOWLY CHANGING DIMENSION), i have some polices which change the intermediate, ex:

Intermediate| policy| date

xx val 01/01/2016

xx val 01/01/2017

yy val 01/01/2018

yy val 01/01/2019

xx val 01/01/2020

and this is what i want:

Intermediate| policy| date | number

xx val 01/01/2016 1

xx val 01/01/2017 1

yy val 01/01/2018 2

yy val 01/01/2019 2

xx val 01/01/2020 3

Re: increment value after any change

Taoufiq_Zarra — Mon, 31 Aug 2020 09:19:50 GMT

Hello @JMAROUF

you can also do :

Data: load *,if(rowno()=1,1,if(previous(Intermediate&'_'&policy)=Intermediate&'_'&policy,peek(number),(peek(number)+1))) as number inline [ Intermediate,policy,date xx,val,01/01/2016 xx,val,01/01/2017 yy,val,01/01/2018 yy,val,01/01/2019 xx,val,01/01/2020 ]

output :

Re: increment value after any change

JMAROUF — Mon, 31 Aug 2020 11:09:12 GMT

Thank you @Taoufiq_Zarra , it works well.

Best regards;