Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator III
Creator III

working with while

hi all

in my model i have a fact table with the following fields :

user_id,

event_date,

event_id,

min_event_date,

max_event_date

i am using :      date(min_event_date+iterno()-1,'YYYYMMDD') as the_date

                             While min_event_date+iterno()-1 <= max_event_date

but result (below) is duplicating date field  , any ideas why?

user_idevent_dateevent_idthe_dateMinDateMaxDate
3170843499.646493056101201902072016032420190207
3170843502.50718758201902072016032420190207
3170843499.646493056101201902062016032420190207
3170843502.50718758201902062016032420190207
3170843499.646493056101201902052016032420190207
3170843502.50718758201902052016032420190207
3170843499.646493056101201902042016032420190207
3170843502.50718758201902042016032420190207
3170843499.646493056101201902032016032420190207
3170843502.50718758201902032016032420190207
3170843499.646493056101201902022016032420190207
3170843502.50718758201902022016032420190207
3170843499.646493056101201902012016032420190207
3170843502.50718758201902012016032420190207
Labels (3)
4 Replies
Highlighted

Re: working with while

What is the exact script you have tried? You see duplicate because your have two different event_id. May be you need to do this in a separate table?

Highlighted
Creator III
Creator III

Re: working with while

thank you Sunny,  below the script.

it is creating a date range for each event_id.. 

load
user_id,
event_date,
event_id,
the_date,
MinDate,
MaxDate,
date(MinDate+iterno()-1,'YYYYMMDD') as the_date
from c:\TableName.qvd(qvd)
While MinDate+iterno()-1 <= MaxDate;

 

 

Highlighted

Re: working with while

May be try this

TableName:
LOAD user_id, event_date, event_id, MinDate, MaxDate from c:\TableName.qvd(qvd); LinkTable: LOAD *,
Date(MinDate + Iterno() - 1,'YYYYMMDD') as the_date While MinDate + IterNo() - 1 <= MaxDate; LOAD DISTINCT MinDate, MaxDate Resident TableName;
Highlighted

Re: working with while

This will create a synthetic key between the two tables, but can be easily avoided by adding a composite key.