Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 :
Row | Process | Date | Some Extra Field |
---|---|---|---|
Row1 | Process1 | 12 Dec 2015 | Null |
Row2 | Process2 | 12 Dec 2015 | Null |
Row3 | Process3 | 12 Dec 2015 | Null |
Row4 | Process1 | 13 Dec 2015 | Null |
Row5 | Process2 | 13 Dec 2015 | Null |
Row6 | Process3 | 13 Dec 2015 | Null |
Row7 | Process1 | 14 Dec 2015 | Null |
Row8 | Process2 | 14 Dec 2015 | Null |
Row9 | Process3 | 14 Dec 2015 | Null |
Row10 | Process1 | 15 Dec 2015 | Null |
Row11 | Process2 | 15 Dec 2015 | Null |
Row12 | Process3 | 15 Dec 2015 | Null |
How to this?
Can anyone help?
Regards,
Anjali Gupta
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;
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;
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
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;
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
Process | Date | Value |
---|---|---|
Process1 | 12 Dec 2015 | Not required |
Process2 | 13 Dec 2015 | Not required |
Process3 | 12 Dec 2015 | Not required |
Process1 | 13 Dec 2015 | Not required |
Process2 | 14 Dec 2015 | Not required |
Process3 | 13 Dec 2015 | Not required |
Process1 | 14 Dec 2015 | Not required |
Process2 | 15 Dec 2015 | Not required |
Process3 | 15 Dec 2015 | Not 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 :
Process | MIN_DATE | MAX_DATE |
---|---|---|
Process1 | 12 Dec 2015 | 14 Dec 2015 |
Process2 | 13 Dec 2015 | 15 Dec 2015 |
Process3 | 12 Dec 2015 | 15 Dec 2015 |
whereas I want
Process | MIN_DATE | MAX_DATE |
---|---|---|
Process1 | 12 Dec 2015 | 15 Dec 2015 |
Process2 | 12 Dec 2015 | 15 Dec 2015 |
Process3 | 12 Dec 2015 | 15 Dec 2015 |
Please help me with your valuable knowledge.
Regards,
Anjali Gupta