topic Re: Increment count using loop in QlikView

Increment count using loop

Wed, 25 Nov 2020 16:16:04 GMT

Hi Guys ,

i have very urgent requirement on looping concept .

Below is my scenario .

Everytime Year column should concatenate with P1 to P13 .

So instead of writing it 13 times can i have in loop .

Please suggest me .

Thanks in advance

load distinct

EmpKey ,

AutonumberHash128(DeptKey) as DepKey,

Year&'P01' As YearPeriod

Resident Emp ;

concatnate

load distinct

EmpKey ,

AutonumberHash128(DeptKey) as DepKey,

Year&'P02' As YearPeriod

Resident Emp ;

concatnate

load distinct

EmpKey ,

AutonumberHash128(DeptKey) as DepKey,

Year&'P03' As YearPeriod

Resident Emp ;

Re: Increment count using loop

sunny_talwar — Thu, 27 Oct 2016 17:54:34 GMT

May be like this:

Table:

LOAD 0 as Dummy

AutoGenerate 0;

For i = 1 to 13

Concatenate (Table)

LOAD DISTINCT

EmpKey,

AutonumberHash128(DeptKey) as DepKey,

Year&'P'&Num($(i), '00') As YearPeriod

Resident Emp ;

NEXT

DROP Field Dummy;

Re: Increment count using loop

johnw — Thu, 27 Oct 2016 23:31:38 GMT

How about this?

LOAD DISTINCT
EmpKey
,autonumberhash128(DeptKey) as DepKey
,Year&'P'&num(iterno(),'00') as YearPeriod
RESIDENT Emp
WHILE iterno()<=13
;

Re: Increment count using loop

johnw — Thu, 27 Oct 2016 23:35:08 GMT

Or this?

LOAD DISTINCT
EmpKey
,autonumberhash128(DeptKey) as DepKey
,Year&'P'&subfield('01,02,03,04,05,06,07,08,09,10,11,12,13',',') as YearPeriod
RESIDENT Emp
;

Perhaps silly when it's just 01 - 13, but useful if it was some other sort of distinct values.

Re: Increment count using loop

Fri, 28 Oct 2016 00:14:57 GMT

John, I guess IterNo() is more optimal method than subfield function method.

Re: Increment count using loop

johnw — Fri, 28 Oct 2016 00:33:47 GMT

I would guess so as well. The other method would be more if we were building values like 2016 Apple, 2016 Grape, and 2016 Banana.

Re: Increment count using loop

Mon, 31 Oct 2016 09:43:38 GMT

Thank you John , its working