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

[Jason_Michaelides]

Check out Jonathan's solution to a similar problem here -

http://community.qlik.com/thread/50519

Hope this helps,

Jason

[#3]

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;

[mazacini]

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

[#5]

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

[mazacini]

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

[#7]

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

[mazacini]

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

[#9 · Accepted solution]

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.

[mazacini]

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