Expand grouped table records into individual records

[mazacini]

I have a table in the following format:

Ref	Start	No of Months	Val
1	Apr-12	2	120
2	Apr-12	3	75
3	May-12	2	100
4	Jun-12	4	50

It indicates that Ref #1 will have a value of 120 for 2 months beginning Apr 12, Ref # 2 will lhave a value of 75 for the 3 months beginning Apr 12 etc

I want to load the table so that there is a separate record for each month, as shown below:

Ref	Start	Mth	Val
1	Apr-12	Apr-12	120
1	Apr-12	May-12	120
2	Apr-12	Apr-12	75
2	Apr-12	May-12	75
2	Apr-12	Jun-12	75
3	May-12	May-12	100
3	May-12	Jun-12	100
4	Jun-12	Jun-12	50
4	Jun-12	Jul-12	50
4	Jun-12	Aug-12	50
4	Jun-12	Sep-12	50

Can anyone help?

Rgds

Joe

[mazacini]

Hi

I am happy to mark your answer as correct. Many thanks for your help.

I have 2 further queries, if you are still willing to help?

1.     I checked my Order_Num field, and it actuall contains some alpahnumeric data which the script does not handle.How do I overcome this problem?

Edit: I think i may have overcome this problem - I changed the "let" statement to a "set" statement for the Order_Num peek, which I think yields the result as text.

2.     This is slightly embarassing. While I can confirm your solution works, i have no idea WHY it works as I am not familiar with the For..Next statement. Could you possibly explian what is going on in the script?

Once again, many thanks for your help. I will understand if I have exhausted your goodwill at this point!

Rgds

Joe

[#3]

Glad I could help.

As to your questions:
1. I'd also keep the let statement and surround the $(vRef) with single quotes like this:
LOAD '$(vRef)' as Ref, etc...
What is actually happening now with the 'Set' is that everything next to the '=' is treated as string and then this content is replacing the $(vRef) in the LOAD.

2. The script is actually looping through all the records from t1 (vNbRows) and for each row, i'm putting the value of the corresponding fields into variables with Peek('FieldName', position, 'TableName').
Then for each of these rows, a number of rows will be created depending on number of months.
So in order_num = 1, the counter j will be evaluated from 0 to 21 which is equal to 22 months.
As for the quarterly case, j will be evaluated from 0 to 23 by adding 3 in each cycle, so in the 1st loop it will be = 0, then 3, then 6, etc...

3. Please let me know if you still have any question regarding this. Sorry if i explained quickly, but i should be going for work and didn't want to leave you hanging with no answer.

I'd also recommend you to search for these terms (let, for...next) in qlikview help as they give examples.

And another helpful step would be to debug the script (from the edit script dialog) so you can actually see what is really happening in each line and how variables are being evaluated.

C u

[mazacini]

Hi Jason

I have a furrher complicaiton i need assistance with, if you can help?

I need to build a table showing the monthly income earned. So, for a quarterly invoice amount, there will be 3 monthly income amounts.

I think I will be able to script that nyself based on your previous advice.

Now for the complication.

If the start date is mid month, the income for that month will be the relevant proportion of a full month income. For a contract with a 24 month term, this would also mean that there would be a 25th month.

So, for a start date 16th July 2012 for a 24 month contract of 2400, Month 1 income would be €51 for 16th to 31st Jul 2012, and Month 25 income would be €49 for 1st to 15th Jul 2014. All other months would be €100.

Can you help?

Ta

Joe