Discussion board where members can get started with QlikView.
I have a table in the following format:
|Ref||Start||No of Months||Val|
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:
Can anyone help?
Solved! Go to Solution.
Check out Jonathan's solution to a similar problem here -
Hope this helps,
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?
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
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
Thanks for taking the time to respond.
Fact is that Jonathan's solution is a little beyond my expertise, so going to go with jp instead.