Create rows in the script

[#1]

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

[#2]

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;

[#3]

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!

[#4]

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

[#5]

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

[#6]

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.

[#7]

Hi

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

[#8 · Accepted solution]

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

[#9]

Thanks Yaman

Will try it

Shirley