working with while

[ilanbaruch]

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_id	event_date	event_id	the_date	MinDate	MaxDate
31708	43499.646493056	101	20190207	20160324	20190207
31708	43502.5071875	8	20190207	20160324	20190207
31708	43499.646493056	101	20190206	20160324	20190207
31708	43502.5071875	8	20190206	20160324	20190207
31708	43499.646493056	101	20190205	20160324	20190207
31708	43502.5071875	8	20190205	20160324	20190207
31708	43499.646493056	101	20190204	20160324	20190207
31708	43502.5071875	8	20190204	20160324	20190207
31708	43499.646493056	101	20190203	20160324	20190207
31708	43502.5071875	8	20190203	20160324	20190207
31708	43499.646493056	101	20190202	20160324	20190207
31708	43502.5071875	8	20190202	20160324	20190207
31708	43499.646493056	101	20190201	20160324	20190207
31708	43502.5071875	8	20190201	20160324	20190207

[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]

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.