topic Re: working with while in QlikView

working with while

ilanbaruch — Sat, 16 Nov 2024 21:27:10 GMT

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

Re: working with while

sunny_talwar — Thu, 07 Feb 2019 15:19:58 GMT

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?

Re: working with while

ilanbaruch — Thu, 07 Feb 2019 15:33:13 GMT

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;

Re: working with while

sunny_talwar — Thu, 07 Feb 2019 15:39:24 GMT

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;

Re: working with while

sunny_talwar — Thu, 07 Feb 2019 15:40:01 GMT

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