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: 
Not applicable

Loop through Dates

Hi Experts,

I have data for a period of time from which i have taken out minimum and maximum date.

Now i have a list of processes. I want to create a dummy data by looping the dates.

Say minimum date is 12 Dec 2015 and Maximum Date is 15 Dec 2015.

and consider processes : process1, process2 and process3.

Then I want to create data in qlikview though script as :

RowProcessDateSome Extra Field

Row1

Process112 Dec 2015Null
Row2

Process2

12 Dec 2015Null
Row3Process312 Dec 2015Null
Row4Process113 Dec 2015Null
Row5Process213 Dec 2015Null
Row6Process313 Dec 2015Null
Row7Process114 Dec 2015Null
Row8Process214 Dec 2015Null
Row9Process314 Dec 2015Null
Row10Process115 Dec 2015Null
Row11Process215 Dec 2015Null
Row12Process315 Dec 2015Null

How to this?

Can anyone help?

Regards,

Anjali Gupta

1 Solution

Accepted Solutions
Kushal_Chawda

So your first step would be to calculate the Min & Max Date

Data:

Load Process

        min(Date) as MIN_DATE

         max(Date) as MAX_DATE

From Table

group by Process;

Final:

NoConcatenate

LOAD Process,

    'Row '&RowNo() as Row,

    date(MIN_DATE+IterNo()-1,'DD MMM YYYY') as Date

Resident Data

While MIN_DATE+IterNo()-1<=MAX_DATE;

DROP Table Data;

View solution in original post

4 Replies
Kushal_Chawda

Data:

LOAD Process,date(date#(MaxDate,'DD MMM YYYY'),'DD MMM YYYY') as MAX_DATE,

date(date#(MinDate,'DD MMM YYYY'),'DD MMM YYYY') as MIN_DATE Inline [

Process,    MaxDate,    MinDate

Process1,    15 Dec 2015,    12 Dec 2015

Process2,    15 Dec 2015,    12 Dec 2015

Process3,    15 Dec 2015,    12 Dec 2015 ];

Final:

NoConcatenate

LOAD Process,

    'Row '&RowNo() as Row,

    date(MIN_DATE+IterNo()-1,'DD MMM YYYY') as Date

Resident Data

While MIN_DATE+IterNo()-1<=MAX_DATE;

DROP Table Data;



Not applicable
Author

Hi Kush,

Thanks for  the quick response

But the minimum date, maximum date and processes provided by me are static.

While the list of processes and date will be dynamic and change as per the data.

Regards,

Anjali Gupta

Kushal_Chawda

So your first step would be to calculate the Min & Max Date

Data:

Load Process

        min(Date) as MIN_DATE

         max(Date) as MAX_DATE

From Table

group by Process;

Final:

NoConcatenate

LOAD Process,

    'Row '&RowNo() as Row,

    date(MIN_DATE+IterNo()-1,'DD MMM YYYY') as Date

Resident Data

While MIN_DATE+IterNo()-1<=MAX_DATE;

DROP Table Data;

Not applicable
Author

Hi Kush,

Thanks a lot for the help.

I have one more query related to this.

As mentioned I have a table with the fields: Process, Date, [Value]

Which looks like

ProcessDateValue
Process112 Dec 2015Not required

Process2

13 Dec 2015Not required
Process312 Dec 2015Not required
Process113 Dec 2015Not required
Process2

14 Dec 2015

Not required
Process313 Dec 2015Not required
Process114 Dec 2015Not required
Process215 Dec 2015Not required
Process315 Dec 2015Not required

And after using the below script :

Data:

Load Process

        min(Date) as MIN_DATE

         max(Date) as MAX_DATE

From Table

group by Process;

I get :

ProcessMIN_DATEMAX_DATE
Process112 Dec 201514 Dec 2015

Process2

13 Dec 201515 Dec 2015
Process312 Dec 201515 Dec 2015

whereas I want

ProcessMIN_DATEMAX_DATE
Process112 Dec 201515 Dec 2015

Process2

12 Dec 201515 Dec 2015
Process312 Dec 201515 Dec 2015

Please help me with your valuable knowledge.

Regards,

Anjali Gupta