Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
No prob at all Joe.
You can find it attached.
As well as a screenshot of the data that is being correctly formatted in here.
Check out Jonathan's solution to a similar problem here -
http://community.qlik.com/thread/50519
Hope this helps,
Jason
Hello Joe,
The below script will help:
I've entered the sample data in an excel sheet.
t1:
LOAD Ref,
Start,
NbrOfMonths,
Val
FROM
Book1.xlsx
(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
t2:
LOAD $(vRef) as Ref,
$(vStart) as Start,
AddMonths($(vStart),$(j)) as Month,
$(vVal) as Val
AutoGenerate 1;
next
next
DROP Table t1;
Hi JP(?)
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
Hi
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
Joe
Hi,
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
Hi
Apologies for being a pain, but I only have Personal Edition. Could you send me the scipt in text format please?
Many thanks
Joe
No prob at all Joe.
You can find it attached.
As well as a screenshot of the data that is being correctly formatted in here.
Hi Jason
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.
Rgds
Joe