12 Replies Latest reply: Jul 16, 2012 9:07 AM by Joe Kirwan RSS

    Expand grouped table records into individual records

    Joe Kirwan

      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

        • Re: Expand grouped table records into individual records
          Jason Michaelides

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

           

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

           

          Hope this helps,

           

          Jason

          • Re: Expand grouped table records into individual records
            Jean-Pierre Bakhache

            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;

             

              • Re: Expand grouped table records into individual records
                Joe Kirwan

                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
                  • Re: Expand grouped table records into individual records
                    Jean-Pierre Bakhache

                    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

                • Re: Expand grouped table records into individual records
                  Joe Kirwan

                  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

                    • Re: Expand grouped table records into individual records
                      Jean-Pierre Bakhache

                      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

                        • Re: Expand grouped table records into individual records
                          Joe Kirwan

                          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

                            • Re: Expand grouped table records into individual records
                              Jean-Pierre Bakhache

                              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.

                                • Re: Expand grouped table records into individual records
                                  Joe Kirwan

                                  Hi

                                   

                                  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!

                                   

                                  Rgds

                                   

                                  Joe

                                    • Re: Expand grouped table records into individual records
                                      Jean-Pierre Bakhache

                                      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.

                                       

                                      C u

                                        • Re: Expand grouped table records into individual records
                                          Joe Kirwan

                                          Hi Jason

                                           

                                          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?

                                           

                                          Ta

                                           

                                          Joe