topic Re: Create rows in the script in QlikView

Create rows in the script

Mon, 24 Jun 2013 22:54:59 GMT

Hi All,

I've data in the following format:

ID	Change Date	Old Value	New Value
1	01/01/2011	Null	A
1	06/01/2011	A	B
1	01/01/2012	B	C

I'd like to create rows in the script where it would take the change date and repeat the old vaue and new value until a new change date comes.

I want a row created for each date.

Like in the above example it should take the change date 01/01/2011 and create records until 05/31/2011 with the same new value and old value. Then on 06/01/2011 new records should be created unttil the next change date.

So the output should show something like this:

01/01/2011 - Null A

01/02/2011 - Null A

01/03/2011 - Null A

........

06/01/2011 - A B

06/02/2011 - A B

....

Can anyone help please?

Thanks

Re: Create rows in the script

Mon, 24 Jun 2013 23:56:23 GMT

This script fills in quantities for missing months. You can use the same methodology to fill in dates.

hth,

Stephen

data:
LOAD * INLINE [
Product, Month, Qty
P1,   1, 20
P1,   2, 15
P1,   3, 13
P1,   4, 30
P1,   6, 23
P1,   7, 20
P1,   8, 18
P1,   9, 28
P2,   1, 10
P2,   2, 60
P2,   3, 14
P2,   4, 30
P2,   6, 2
P2,   7, 20
P2,   11, 29
P2,   12, 40
P3,   1, 20
P3,   2, 10
P3,   3, 13
P3,   4, 30
P3,   5, 25
P3,   6, 2
];

tmp:
LOAD DISTINCT Product RESIDENT data; // List of all Products
JOIN (tmp) LOAD DISTINCT Rowno() as Month AUTOGENERATE 12; // List of all Months
JOIN (tmp) LOAD DISTINCT // Actual data
Product,
Month,
Qty
RESIDENT data;

//
NOCONCATENATE
result:
LOAD DISTINCT
Product,
Month,
if(isnull(Qty) and Product=peek('Product'), peek('Qty'), Qty) as Qty
RESIDENT tmp
ORDER BY Product, Month;
//
DROP TABLES tmp, data;

Re: Create rows in the script

Tue, 25 Jun 2013 01:42:20 GMT

Hi, Thanks for your reply,

I am not sure how this approach would work, since month can be definite and so you are generating all the data easily. But i have data that have one date in 2011 and other in 2012.

JOIN (tmp) LOAD DISTINCT Rowno() as Month AUTOGENERATE 12; // List of all Months

How can I generate row for each date?

Thanks!

Re: Create rows in the script

Tue, 25 Jun 2013 03:44:16 GMT

so basically for each ID.. there is a different range of start and end date!

Re: Create rows in the script

Tue, 25 Jun 2013 05:38:39 GMT

Hi,

Please find the attached example for your reference.

In-case of any query let me know.

Hope this will suffice ur requirement.

Regards,

Nitin Jain

Re: Create rows in the script

Tue, 25 Jun 2013 06:36:31 GMT

Hi Nitin,

Thanks very much for your reply. Sorry if I wasn't clear before in my explaination. Here is how my data looks:

ID	Change Date	New Value
1	Jan-01-2011	A
1	Jun-01-2011	B
1	Feb-01-2012	C
2	Feb-01-2011	A
2	Mar-01-2012	B
3	May-01-2013	D
3	Dec-06-2013	E

In the example you posted, you took the max and min of change date. But I have hunderds of IDS, how should I calculate that? Also, my output should have a row for each calendar date. Here is what I am trying to achieve:

Example for ID=1, there is a record created for each date between the change dates. Similarly same records will be created for other ID's. See the output below:

ID	Change Date	New Value
1	Jan-01-2011	A
1	Jan-02-2011	A
1	Jan-03-2011	A
…...............
1	May-31-2011	A
1	Jun-01-2011	B
1	Jun-02-2011	B
1	Jun-03-3011	B
……...
1	Jan-31-2011	B
1	Feb-01-2012	C
2	Feb-01-2011	A

I do have old value for each row, but all I care is the new value on each date. I hope I am clear this time. Thanks again for your reply.

Re: Create rows in the script

Sun, 21 Jul 2013 13:04:18 GMT

did you manage to get this working and generate new rows for the dates as you described above ?

I would like to do something similar

thanks

Shirley

Re: Create rows in the script

Sun, 21 Jul 2013 20:17:07 GMT

Hi Shirley,

Yes I was able to do it. Follow this thread and refer to Gysbert solution.

http://community.qlik.com/thread/85375

Thanks,

Yaman

Re: Create rows in the script

Sun, 21 Jul 2013 21:07:57 GMT

Thanks Yaman

Will try it

Shirley