topic Generate Periodic Datas in QlikView

Generate Periodic Datas

Wed, 19 Aug 2015 13:06:59 GMT

Hi every one,

I need to create periodic data from a table, I try for a For Loop without sucess, I think maybe AutoGenerate can help me but I think this goes far beyond me...

So, here is my issue :

I have a table with this kind of datas :

ID (Primary key)

Start Date

Nb of echeance

Periodicy (in month, 1 or 3)

For exemple :

ID ---- Date ---- NBE ---- PERIO

1 ---- 10/10/2014 --- 5 ---- 3

2 ---- 01/01/2015 ----10 ----1

I Want to generate a table with this kind of data :

ID ---- DATE

1 ---- 10/10/2014

1 ---- 10/01/2015

1 ---- 10/04/2015

1 ---- 10/07/2015

1 ---- 10/10/2015

2 ---- 01/01/2015

2 ---- 01/02/2015

2 ---- 01/03/2015

2 ---- 01/04/2015

2 ---- 01/05/2015

2 ---- 01/06/2015

2 ---- 01/07/2015

2 ---- 01/08/2015

2 ---- 01/09/2015

2 ---- 01/10/2015

Could you help me with this ?

Thanks !

Re: Generate Periodic Datas

Wed, 19 Aug 2015 14:40:10 GMT

Here is What I Try :

ABONNEMENTS:

SQL SELECT PABID,

PABDATEDEBUT

,PABNBECHEANCES

,PABPERIODICITE

,PABNBECHEXE

FROM PIECEABONNEMENT;

For i=0 to NoOfRows('ABONNEMENTS')-1

Let StartDate=Peek('ABONNEMENTS.PABDATEDEBUT',$(i),'ABONNEMENTS');

Let Perio=Peek('ABONNEMENTS.PABPERIODICITE',$(i),'ABONNEMENTS');

Let NbEchenance=Peek('ABONNEMENTS.PABNBECHEANCES',$(i),'ABONNEMENTS');

If ($(NbEchenance)>24) Then

Set NbEchenance=24;

End If

For j=1 to $(NbEchenance)

Let StartDate=AddMonths('$(StartDate)',$(Perio));

FORECAST:

LOAD

'$(StartDate)' as Date,

Peek('ABONNEMENTS.PABID',$(i),'ABONNEMENTS') as PABID;

Next j;

Next i

This script seem to work in debug mode, I have no error and the loops are ok, but my tale FORECAST is not available in QlikView.

Re: Generate Periodic Datas

sasiparupudi1 — Thu, 20 Aug 2015 19:40:50 GMT

please try this script

temp:

load ID,Date(Date#(Date,'DD/MM/YYYY')) as MyDate,num(NBE) as NBE,num(PERIO) as PERIO inline

[

ID , Date , NBE , PERIO

1 ,10/10/2014,5,3

2 ,01/01/2015,10,1

]

;

NoConcatenate

New:

load ID,NBE,PERIO,AddMonths(MyDate,(IterNo()-1)*PERIO) as MyDate //,IterNo() as x

while IterNo()<=NBE;

load * Resident temp order by MyDate;

drop Table temp;

Re: Generate Periodic Datas

Tue, 24 Nov 2015 16:36:54 GMT

Hi Sasidhar,

I'm sorry. I'm not a pro, I have a error message with your code :

Field not found

load * Resident temp order by MyDate

Could you help me ?