Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
ilanbaruch
Specialist
Specialist

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
sunny_talwar

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?

ilanbaruch
Specialist
Specialist
Author

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;

 

 

sunny_talwar

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;
sunny_talwar

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