topic Making new field with loop in QlikView

Making new field with loop

Leo2 — Sat, 16 Nov 2024 20:51:48 GMT

Hi guys. I'm trying to make a new field in load script and need to automate the process. Basically I have a table of people's names and their dates of birth (Year). I would like to make a new field (Century) based on (Year). Sounds easy, but...

All I have done so far is:

FOR counter=0 to 20
Table_name:
LOAD
FullName,
if(
BirthYear>($(counter)*100) and BirthYear<($(counter)*100+100),
$(counter)+1
)
as Century
Resident Table1;
NEXT

And as the result I have a huge table (20 times bigger than needed) with a lot of null data. Can anybody just give a clue how is it possible to manage such a task? Many thanks.

Re: Making new field with loop

dplr-rn — Wed, 15 May 2019 14:51:26 GMT

Why do you need a loop for calculating the century. Why not a simple extra column like below

if(Mod(BirthYear,100)=0,BirthYear/100,floor(BirthYear/100)+1) as Century

that logic will give 20 for 1998 and 2000,21 for 2001

Maybe i misunderstood the problem

Re: Making new field with loop

Leo2 — Thu, 16 May 2019 06:49:23 GMT

Thanks for the hint. I've just corrected the formula to work good with both + and - dates. Here it is:

if(
birth_year>0,
FLOOR(birth_year/100) +1,
FLOOR(birth_year/100)
)
AS Century,