The below script will help:
I've entered the sample data in an excel sheet.
(ooxml, embedded labels, table is Sheet2);
let vNbrRows = NoOfRows('t1');
for i = 0 to $(vNbrRows)-1
let vRef = Peek('Ref',$(i),'t1');
let vStart = Peek('Start',$(i),'t1');
let vNbrMonths = Peek('NbrOfMonths',$(i),'t1');
let vVal = Peek('Val',$(i),'t1');
for j = 0 to $(vNbrMonths)-1
LOAD $(vRef) as Ref,
$(vStart) as Start,
AddMonths($(vStart),$(j)) as Month,
$(vVal) as Val
DROP Table t1;
That seems to work. Thank you.
One problem - there seems to be a problem with my month formats.
The table box below illustrates my problem.
If you had any ideas on what the problem is, I'd love to hear them?
Ref Start Month Val 1 00/01/1900 00/01/1900 120 1 00/01/1900 31/01/1900 120 2 00/01/1900 00/01/1900 75 2 00/01/1900 31/01/1900 75 2 00/01/1900 29/02/1900 75 3 00/01/1900 00/01/1900 100 3 00/01/1900 31/01/1900 100 4 00/01/1900 00/01/1900 50 4 00/01/1900 31/01/1900 50 4 00/01/1900 29/02/1900 50 4 00/01/1900 30/03/1900 50
Yeah, this is due to the start date format in the datasource.
In my excel sheet, I've entered formatted dates as 4/1/2012 for April 2012, etc...
This time I left the cells formatted as Text and entered Apr-12, May-12 which lead me to change the way i'm reading the 'Start' field into vStart variable:
let vStart = num(Date#(Peek('Start',$(i),'t1'), 'MMM-YY'));
So it depends on how your start date is formatted.
If you want, you can attach a sample of your data to help you out with the correct formatting
In fact, my start field (in my live data) is formatted dd/mm/yyyy.
Instead of the new date values being in "Month", could they be formatted in the same way (dd/mm/yyy) in a field called "Date"? I can always add a Month field at a later stage.
Also, (I hope I am not presuming too much), could you suggest a script to create "Date" in Quarterly and Yearly increments when so indicated by Frequency field in the attached?
Many thanks for your help so far.
Sample data attached
Test2.xlsx 9.0 K
You can check the document attached that should solve your problem based on your attached excel sheet.
As well as the quarterly and yearly increments by using "Step" in the For...Next.
If you want to add other frequencies you just have to modify the "if condition" in the script as you'll see.
The attached document shows the dates correctly formatted.
But in case you still encounter issues, you can always solve that by just fine tuning the expressions in the script.
Hope this proves helpful
Test2_For_Next_Step.qvw 154.2 K
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!
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.
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?