Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
mazacini
Contributor III

Expand grouped table records into individual records

I have a table in the following format:

RefStartNo of MonthsVal
1Apr-122120
2Apr-12375
3May-122100
4Jun-12450

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:

RefStartMthVal
1Apr-12Apr-12120
1Apr-12May-12120
2Apr-12Apr-1275
2Apr-12May-1275
2Apr-12Jun-1275
3May-12May-12100
3May-12Jun-12100
4Jun-12Jun-1250
4Jun-12Jul-1250
4Jun-12Aug-1250
4Jun-12Sep-1250

Can anyone help?

Rgds

Joe

1 Solution

Accepted Solutions
Not applicable

Re: Expand grouped table records into individual records

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.

12 Replies
jason_michaelid
Honored Contributor II

Re: Expand grouped table records into individual records

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

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

Hope this helps,

Jason

Not applicable

Re: Expand grouped table records into individual records

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
Contributor III

Re: Expand grouped table records into individual records

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?

RefStartMonthVal
100/01/190000/01/1900120
100/01/190031/01/1900120
200/01/190000/01/190075
200/01/190031/01/190075
200/01/190029/02/190075
300/01/190000/01/1900100
300/01/190031/01/1900100
400/01/190000/01/190050
400/01/190031/01/190050
400/01/190029/02/190050
400/01/190030/03/190050
Not applicable

Re: Expand grouped table records into individual records

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
Contributor III

Re: Expand grouped table records into individual records

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

Not applicable

Re: Expand grouped table records into individual records

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
Contributor III

Re: Expand grouped table records into individual records

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

Not applicable

Re: Expand grouped table records into individual records

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
Contributor III

Re: Expand grouped table records into individual records

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

Community Browser